VBA：区域引用数组(VBA: Array of Range References)

在VBA中，我想创建区域引用数组。 这是我当前的尝试：

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

当我尝试将多个引用存储在数组中，我得到的是这样的：

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

我曾试图使colRange一个变种，但似乎没有任何工作。 不解决我通过谷歌或StackOverflow的发现似乎解决这个问题。

Answer 1:

另外我上面的评论，下面是一个例子

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

避免使用.Select 。 像我在上面所做的范围内直接执行的操作。 有趣的阅读

文章来源: VBA: Array of Range References