Cell Referencing Error - Method 'Range' of object'_Worksheet' failed

[alexb523]

I have seen examples of this issue out there, but i am having problems solving it when it comes to solving my own.

Please take a look at my code below, it is bugging in the array formula.

    Dim TableNames As Worksheet
    Dim ss As Worksheet
    Dim ssclaim As Worksheet
    Dim sspercent As Worksheet
    Dim lrTableNames As Long
    
    Set TableNames = b1.Sheets("TableNames")
    Set ss = b1.Sheets("SS1617_TP164")
    Set ssclaim = b1.Sheets("SSbyClaim1617_TP177")
    Set ssprecent = b1.Sheets("SSpercentile1617_TP198")
    lrTableNames = TableNames.Range("B" & Rows.Count).End(xlUp).Row
    Dim Lastrow As Long
    Lastrow = ss.Range("B" & Rows.Count).End(xlUp).Row


    For Each iRow In ss.Range("L2:L" & Lastrow)
        ss.Range("L" & iRow).FormulaArray = _
        "=INDEX(TableNames!$J$2:$J" & lrTableNames & ",MATCH(1,(TableNames!$D$2:$D" & lrTableNames & "=$C" & iRow & ")*(TableNames!$E$2:$E" & lrTableNames & "=$B" & iRow & ")*(TableNames!$A$2:$A" & lrTableNames & "=""Average Overall Scale Score""),0))"
    Next iRow

 

[Dryver14]

For Each iRow In ss.Range("L2:L" & Lastrow)

Dim irow as long

For irow =2 to to lastrow


ss.Range("L" & iRow).FormulaArray = _
"=INDEX(TableNames!$J$2:$J" & lrTableNames & ",MATCH(1,(TableNames!$D$2:$D" & lrTableNames & "=$C" & iRow & ")*(TableNames!$E$2:$E" & lrTableNames & "=$B" & iRow & ")*(TableNames!$A$2:$A" & lrTableNames & "=""Average Overall Scale Score""),0))"
Next iRow

 

[StephenCrump]

Or, without looping:

With ss.Range("L2:L" & Lastrow)
    .Formula = "=INDEX(TableNames!$J$2:$J$" & lrTableNames & ",MATCH(1,(TableNames!$D$2:$D$" & lrTableNames & "=$C2)*(TableNames!$E$2:$E$" & lrTableNames & "=$B2)*(TableNames!$A$2:$A$" & lrTableNames & "=""Average Overall Scale Score""),0))"
    .FormulaArray = .FormulaR1C1
End With

By the way, you have a typo here, which will cause problems if you use sspercent later in your code:

Dim sspercent As Worksheet

Set ssp[COLOR=#ff0000][B]re[/B][/COLOR]cent = b1.Sheets("SSpercentile1617_TP198")

A good reason to always use Option Explicit!