使用数组时方法"对象范围"_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