VBA code to autofill formula to last cell in row

[lwebbie]

I am trying to write a VBA code that will autofill the formula to the last active cell in the column. The range varies each week, sometimes there will be 3 rows of data other times there will be more. Ideally the code is dynamic and will adjust without me having to manually update the code.

Right now the range is Range("R8:R10").

Sub Macro2Device()
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],XHSCompletedOrders.xlsx!R2C7:R3341C21,2,FALSE)"
Selection.AutoFill Destination:=Range("R8:R10")
Range("R8:R10").Select
End Sub

Thanks

 

[JLGWhiz]

Sub Macro2Device()
Dim rng As Range
 With ActiveSheet
     Set rng = .Range("R8", .Cells(Rows.Count, "R").End(xlUp))
 End With
 ActiveCell.FormulaR1C1 = _
 "=VLOOKUP(RC[-11],XHSCompletedOrders.xlsx!R2C7:R3341C21,2,FALSE)"
 Selection.AutoFill Destination:=rng
 End Sub

 

[lwebbie]

That added the formula to all the cells in column R with R8 being the last cell (so cells R1:R8 now have the formula). I need the formula to always start in cell R8 and then paste down however many rows of data there are. If it needs a cell to reference for data it can reference column Q.

 

[SQUIDD]

Hi

you could try this

Sub loop_formula()
lastrow = Range("Q" & Rows.Count).End(xlUp).Row
    For a = 8 To lastrow
        With Range("a" & a)
            .FormulaR1C1 = "=VLOOKUP(RC[-11],XHSCompletedOrders.xlsx!R2C7:R3341C21,2,FALSE)"
        End With
    Next
End Sub

test it on a copy of your workbook

dave

 

[Michael M]

Maybe

Sub MM1()
Range("R8:R" & Cells(Rows.Count, "Q").End(xlUp).Row).Formula = "=VLOOKUP(G7,XHSCompletedOrders.xlsx!$G$2:$U$3341,2,FALSE)"
End Sub

 

[JLGWhiz]

I see Michael has made it simple, but this is just for the record to autofill.

Sub Macro2Device()
Dim rng As Range
 With ActiveSheet
     Set rng = .Range("R8", .Cells(Rows.Count, "Q").End(xlUp).Offset(, 1))
 End With
 ActiveCell.FormulaR1C1 = _
 "=VLOOKUP(RC[-11],XHSCompletedOrders.xlsx!R2C7:R3341C21,2,FALSE)"
 Selection.AutoFill Destination:=rng
 End Sub

 

[lwebbie]

Thanks everyone these seemed to work perfectly.