VBA: Array of Range References

Question

In VBA, I am trying to create an array of Range References. Here's my current attempt:

Dim columnName()  As String
Dim colIndex()    As Long
Dim colRange()    As Range

colCount = 10
ReDim columnName(colCount)
ReDim colIndex(colCount)
ReDim colRange(1 To colCount)

columnName(ID) = "ID"
'etc

For i = 1 To UBound(columnName)
    colIndex(i) = WS.Range("A1", "ZZ1").Find(columnName(i), LookIn:=xlValues, MatchCase:=False).column
    colRange(i) = WS.Range(Cells(2, colIndex(i)), Cells(LastRowIndex, colIndex(i)))
    If 1 = 1 Then 'debugging
        Application.ScreenUpdating = True
        Debug.Print colRange(i).Value
        Debug.Print colRange(i).Address
        colRange(i).Select
        Application.ScreenUpdating = False
    End If

When I try to store multiple references in an array, I get something like this:

i = 1
colIndex(i) = 8
Cells(2, colIndex(i)) = 123
Cells(LastRowIndex, colIndex(i)) =789
colRange(i) = Nothing

I have tried making colRange a variant, but nothing seems to work. No solutions I found via google or StackOverflow seemed to address this.

Answer 1

Further to my comment above, here is an example

Sub Sample()
    Dim columnName() As String
    Dim rng As Range

    colCount = 10
    ReDim columnName(colCount)

    ID = 1

    columnName(ID) = "A"

    'MsgBox Cells(1, columnName(1)).Address
    Cells(1, columnName(1)).Value = "Blah Blah"

    Set rng = Cells(1, columnName(1))

    With rng
        MsgBox .Address
        .Value = "Something"
        '~~> Do whatever you want to do with that range here
    End With
End Sub

Avoid the use of .Select. Directly perform the action on the range like I have done above. INTERESTING READ