VBA使用数组批量写入公式时报错‘Method 'Range of Worksheet' failed’的修复请求

Let's break down why your optimized code is throwing that error and fix it step by step:

Key Issues in Your Current Code #

1. Incorrect Array Indexing:
   VBA arrays default to 0-based indexing, but you're looping from i = 1 To 12. Your ColArray and BaseArray each have 12 elements, so valid indices are 0 to 11—looping to 12 will try to access an element that doesn't exist, causing unexpected behavior.

2. Bad String Concatenation for Range Address:
   When you write sh.Range("ColArray(i)3:ColArray(i)"&lRow), VBA treats ColArray(i) as literal text instead of evaluating the variable. You need to break out of the string quotes to insert the array values using the & concatenation operator.

3. Formula String Doesn't Evaluate the Array Variable:
   Similarly, "=BaseArray(i)2" is just a static string to VBA—you need to concatenate the actual value from BaseArray into the formula string to generate the correct cell reference.

4. Implicit Equal Sign Risk:
   Your initial redundant code uses Formula = "Q2" which Excel might auto-correct to =Q2, but it's safer to explicitly include the = in your formula to avoid edge cases where Excel doesn't recognize it as a formula.

Fixed Code #

Here's the revised version with all issues addressed:

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")
    
    ' Loop using the array's actual bounds (0 to 11)
    For i = LBound(ColArray) To UBound(ColArray)
        ' Correctly build the range address by concatenating the array value
        sh.Range(ColArray(i) & "3:" & ColArray(i) & lRow).Formula = _
            "=" & BaseArray(i) & "2" ' Build the formula with the base column reference
    Next i
Next sh

Bonus: Even More Efficient Approach #

If you want to make this even faster (especially with large datasets), you can write the entire range at once instead of looping through each column. Since your formulas follow a consistent pattern (each column references the column to its left, row 2), you can use R1C1 notation to eliminate arrays entirely:

Dim wb As Workbook: Set wb = Workbooks("A.xlsx")
Dim sh As Worksheet
Dim lRow As Long
Dim targetRange As Range

For Each sh In wb.Worksheets
    lRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    ' Define the full target range from R3 to AC[last row]
    Set targetRange = sh.Range("R3:AC" & lRow)
    ' Write the formula to the entire range in one go
    targetRange.FormulaR1C1 = "=R2C[-1]"
Next sh

R2C[-1] translates to "row 2, column one to the left"—it's a dynamic way to reference the adjacent column's row 2, which perfectly matches your original pattern.

内容的提问来源于stack exchange，提问作者Shane