VBA code to autofill formula to last cell in row

lwebbie

New Member
Joined
Feb 28, 2017
Messages
24
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,306
Office Version
2013
Platform
Windows
Code:
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
 
Last edited:

lwebbie

New Member
Joined
Feb 28, 2017
Messages
24
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

Well-known Member
Joined
Jan 2, 2009
Messages
1,667
Hi

you could try this

Code:
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
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,201
Office Version
2013
Platform
Windows
Maybe

Code:
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
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,306
Office Version
2013
Platform
Windows
I see Michael has made it simple, but this is just for the record to autofill.

Code:
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
 

Forum statistics

Threads
1,082,446
Messages
5,365,567
Members
400,838
Latest member
Renedescartes

Some videos you may like

This Week's Hot Topics

Top