Autofill to last row based on activecell

Asked Apr 27 '23 at 16:51

Active Apr 27 '23 at 16:59

Viewed 46 times

I'm trying to automate a spreadsheet as much as possible but I'm running into issues with filling down to the last row, and I can't find a solution that works for me.

The number of rows containing data changes on a monthly basis, so I need a dynamic range rather than static.

This code works for a static range:

Sub InsertFormula()
    ActiveCell.Formula = "=IFERROR(VLOOKUP(A4, 'SI Report'!$A$2:$F$158, 4, 0), 0)"
        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveCell.AutoFill Destination:=Range("F4:F200"), Type:=xlFillValues
End Sub

So I've been trying to build on it to convert to a dynamic range. I've tried the below, but I get an error

"Run-time error '1004': Method 'Range' of object '_Global' failed":

Sub InsertFormula()
    ActiveCell.Formula = "=IFERROR(VLOOKUP(A4, 'SI Report'!$A$2:$F$158, 4, 0), 0)"
        Dim LastRow As Long
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ActiveCell.AutoFill Destination:=Range(ActiveCell & LastRow), Type:=xlFillValues
End Sub

Can anyone help, please?

edited Apr 27 '23 at 16:52

braX

11,506
5
20
33

asked Apr 27 '23 at 16:51

Katy Adams

2

`Range("F4:F" & LastRow)="=IFERROR(VLOOKUP(A4, 'SI Report'!$A$2:$F$158, 4, 0), 0)"` – Davesexcel Apr 27 '23 at 16:55
4

`Range(ActiveCell, Cells(LastRow,ActiveCell.Column))` – Scott Craner Apr 27 '23 at 16:55
2

There's no need to `AutoFill`. Just write the formula to the entire range in one go. – BigBen Apr 27 '23 at 16:57

Autofill to last row based on activecell

0 Answers0