0

I have a dynamic range and I'd like to fix up one range of data using select case, paste it 20 columns right, then concatenate two columns, the fixed up range column and another column in the dynamic range set, which will be pasted 20 columns right, or where the fixed range was pasted.

I'm having trouble actually concatenating them. I have the select case part working, but I'm not sure how to actually concatenate them.

Right now I have

Private Sub PGA(colNum As Long, LastRow As Long, foundPass As Range, List As Range)
    Dim People As Integer
    Dim Gift As Integer
    Dim PeopleRange As String
    Dim GiftRange As String
    Dim List2 As Range
    Dim AgeRange As String


    For Each List In Range(Cells(3, colNum + 14), Cells(LastRow, colNum + 14))
        People = Mid(List.Value, 1, 1)
        Select Case People
            Case 1
                PeopleRange = "1 Person"
            Case 2
                PeopleRange = "2 People"
            Case 3
                PeopleRange = "3 People"
            Case 4
                PeopleRange = "4 People"
            Case 5
                PeopleRange = "5 People"
            Case Is >= 6
                PeopleRange = "6+ People"

        End Select
        Gift = Mid(List.Value, 5, 1)
        Select Case Gift
           Case 1
                GiftRange = "1 Gift"
            Case 2
                GiftRange = "2 Gifts"
            Case 3
                GiftRange = "3 Gifts"
            Case 4
                GiftRange = "4 Gifts"
            Case 5
                GiftRange = "5 Gifts"
            Case Is >= 6
                GiftRange = "6+ Gifts"
        End Select
        For Each List2 In Range(Cells(3, colNum + 15), Cells(LastRow, colNum + 15))
            List2.Value = AgeRange
            List.Offset(0, 20).Value = PeopleRange & "/" & GiftRange & "/" & AgeRange
        Next List2
    Next List

End Sub
David Zemens
  • 53,033
  • 11
  • 81
  • 130
ThePangloss
  • 31
  • 5
  • Is this the `For Each List2` loop? What seems to be the problem with the output? – David Zemens Jul 06 '16 at 18:31
  • I notice also that you haven't assigned any value to `AgeRange`. And, this may be pedantic, but naming variables like `...Range` when the data type is `String` is potentially confusing from a code-maintenance/troubleshooting perspective. – David Zemens Jul 06 '16 at 18:34
  • You may simply need to change: `List2.Value = AgeRange` to `AgeRange = List2.Value`. Or, you can omit `AgeRange` altogether; delete that line, and then do simply: `List.Offset(0, 20).Value = PeopleRange & "/" & GiftRange & "/" & List2.Value` – David Zemens Jul 06 '16 at 18:45

0 Answers0