Method 'Range of object'_worksheet' failed when using array

Question

I tried to write the formula into columns and have the following naive method that does the work:

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"

I felt that I could use the array to make these shorter or more efficient, so I searched on the site and tried the following code based on my understanding:

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

I got titlementioned error message after running the code, can someone point out how to fix the code? Many thanks in advance.

@Warcupine, thank you for point this out, I got "Application-defined or object-defined error", any idea why this happened? — Shane
– Shane, Commented Mar 30, 2021 at 15:27
"=" & BaseArray(i) & 2 You also need to change your iterator to For i = 0 To 11 arrays are 0 indexed, unless they come from a range. — Warcupine
– Warcupine, Commented Mar 30, 2021 at 15:31
@Warcupine, thank you, so anything around the array should be separated with a quotation mark. Now it works. — Shane
– Shane, Commented Mar 30, 2021 at 16:35
Yeah as you had it it was a string that looked like your array variables so your range object didn't know what to do with that string and the formula was looking for a function by that name and couldn't find it. — Warcupine
– Warcupine, Commented Mar 30, 2021 at 17:00

VBasic2008 · Accepted Answer · 2021-03-30 16:23:08Z

2

Array vs Option Base

An array created with the Array function 'gets' its limits depending on the Option Base Statement. If it is 0 (default), then the array is zero-based. If it is 1, then the array is one-based. It is best to either use LBound and UBound or declare the array as VBA.Array in which case it is always zero-based (BTW an array created with the Split function is always zero-based).

Short But Sweet

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

Loop and Array Practice

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

edited Mar 30, 2021 at 16:23

answered Mar 30, 2021 at 15:35

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shane Over a year ago

Thank you so much for your answer. Now it's pretty clear to me how it works.

Collectives™ on Stack Overflow

Method 'Range of object'_worksheet' failed when using array

1 Answer 1

Array vs Option Base

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Array vs Option Base

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related