Need a VB Autofill solution

[MtyAVATAR]

I pull reports that has data in columns A thru AD but the rows of data can vary.

I am trying to write a simple VB code to paste 8 formulas in rows AE thru AL and paste them down to the end of the data set but I keep getting an error on the 4th line where I try to define "LRow". If I remove this line, the VB works but only copys the formula down 19 rows instead of to the end of the data set.

I am sure it is something simple but this is my first year working with VB and self teaching myself as I go. I have spent almost 2 hours trying to resolve this error to no avail.

Sub FillDown()

    Dim strFormulas(1 To 8) As Variant
    Dim LRow As Long    
         LRow = Range("A:A" & Rows.Count).End(x1Up).Row
    
    With ThisWorkbook.Sheets("Sheet1")

        'ZGLR
        strFormulas(1) = "=IFERROR(N2/L2,0)"
        
        'DIS
        strFormulas(2) = "=IFERROR(AB2/L2,0)"
        
        'ZIBE
        strFormulas(3) = "=IFERROR(T2/SUM(N2+AB2),0)"
        
        'ZICM
        strFormulas(4) = "=IFERROR(U2/L2,0)"
        
        'ZIGN
        strFormulas(5) = "=IFERROR(V2/SUM(N2+AB2+T2+U2),0)"
        
        'ZOBE
        strFormulas(6) = "=IFERROR(W2/SUM(N2+AB2+T2+U2+V2),0)"
        
        'ZOCM
        strFormulas(7) = "=IFERROR(X2/L2,0)"
        
        'ZOGM
        strFormulas(8) = "=IFERROR(Y2/SUM(N2+v),0)"

     .Range("AE2:AL2").Formula = strFormulas
     .Range("AE2:AL2" & LRow).FillDown
        
    End With

End Sub

 

[theBardd]

Try this

LRow = Cells(Rows.Count, "A").End(xlUp).Row

 

[steve the fish]

Place your LR after:

With ThisWorkbook.Sheets("Sheet1")

then use a dot

LRow = .Range("A" & Rows.Count).End(xlUp).Row

also this line is incorrect:

.Range("AE2:AL2" & LRow).FillDown

Change it to:

.Range("AE2:AL" & LRow).FillDown

 

[steve the fish]

Looks like this:

strFormulas(8) = "=IFERROR(Y2/SUM(N2+v),0)"

should be:

strFormulas(8) = "=IFERROR(Y2/SUM(N2+V2),0)"

 

[MtyAVATAR]

Thank you Steve the Fish. Works perfectly.