arrays - 使用数组时方法 'Range of object' _worksheet' 失败

Question

我试图将公式写入列，并使用以下简单的方法来完成工作:

sh.Range("R3:R" & lRow).Formula = "Q2"
sh.Range("S3:S" & lRow).Formula = "R2"
sh.Range("T3:T" & lRow).Formula = "S2"
sh.Range("U3:U" & lRow).Formula = "T2"
sh.Range("V3:V" & lRow).Formula = "U2"
sh.Range("W3:W" & lRow).Formula = "V2"
sh.Range("X3:X" & lRow).Formula = "W2"
sh.Range("Y3:Y" & lRow).Formula = "X2"
sh.Range("Z3:Z" & lRow).Formula = "Y2"
sh.Range("AA3:AA" & lRow).Formula = "Z2"
sh.Range("AB3:AB" & lRow).Formula = "AA2"
sh.Range("AC3:AC" & lRow).Formula = "AB2"

我觉得我可以使用数组来使这些更短或更高效，所以我在网站上搜索并根据我的理解尝试了以下代码:

Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
Dim sh As Worksheet
Dim lRow As Long
Dim i As Integer
Dim ColArray As Variant
Dim BaseArray As Variant

For Each sh In wb.Worksheets

lRow = sh.Cells(sh.Rows.Count,1).End(xlUp).Row

ColArray = Array("R","S","T","U","V","W","X","Y","Z","AA","AB","AC")
BaseArray = Array("Q","R","S","T","U","V","W","X","Y","Z","AA","AB")

  For i = 1 To 12
    sh.Range("ColArray(i)3:ColArray(i)"&lRow).Formula = "=BaseArray(i)2"
  Next i

Next sh

运行代码后我收到标题提到的错误消息，有人可以指出如何修复代码吗？提前谢谢了。

Answer 1

数组与选项库

使用 Array function 创建的数组根据 Option Base Statement 来“获取”它的限制.如果它是 0(默认值)，那么数组是从零开始的。如果为 1，则数组是从 1 开始的。最好使用 LBound和 UBound或将数组声明为 VBA.Array在这种情况下，它总是从零开始(顺便说一句，使用 Split function 创建的数组总是从零开始)。

短而甜

Sub ArrayShortened()

    Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
    
    Dim ws As Worksheet
    Dim lRow As Long
    
    For Each ws In wb.Worksheets
        lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        ws.Range("R3").Resize(lRow - 2, 12).Formula = "=Q2" ' 3 - 1 = 2
    Next ws

End Sub

循环和数组练习

Sub ArrayShortened2()

    Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
    Dim ws As Worksheet
    Dim lRow As Long
    Dim j As Long
    Dim ColArray As Variant
    Dim BaseArray As Variant
    
    For Each ws In wb.Worksheets
    
        lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        
        ColArray = Array("R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC")
        BaseArray = Array("Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB")
        
        For j = LBound(ColArray) To UBound(ColArray)
            ws.Range(ColArray(j) & "3", ColArray(j) & lRow).Formula = "=" & BaseArray(j) & "2"
            'or:
            'ws.Range(ColArray(j) & "3:" & ColArray(j) & lRow).Formula = "=" & BaseArray(j) & "2"
        Next j
    
    Next ws

End Sub