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.

Change `Dim colRange() As Range` to `Dim colRange() As String` — Siddharth Rout, Aug 18 '14 at 21:07
When I did that, it just gives `Compile error: Invalid qualifier` for the `Debug.Print` statements and the `colRange(i).Select` statement. — Phillip, Aug 18 '14 at 21:10
I added few other things so you may have to refresh the page — Siddharth Rout, Aug 18 '14 at 21:15

score 1 · Accepted Answer · edited May 23 '17 at 12:29

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

edited May 23 '17 at 12:29

Community

1
1

answered Aug 18 '14 at 21:10

Siddharth Rout

147,039
17
206
250

I feel stupid. I played with your code, change `rng` to `rng()`, change `Set rng...` to `Set rng(1) = Range(...)`, and kept comparing it to mine. Eventually saw the tiny difference: I was using `colRange(i) = Range(...)` when I needed to be using `SET colRange(i) = Range(...)`. Thank you for taking the time to write up this example, it was certainly helpful. Thanks for the link as well! – Phillip Aug 18 '14 at 21:26

VBA: Array of Range References

1 Answers1

Linked