VBA Perform Autofill formula in specified column name/location

[explrll]

Hello,

I have a VBA macro that will insert a new column (titled updated col..) to the right of the specified column name. Then I use a VLOOKUP and autofill in the new column to provide the updated values. The column location of the updated column is variable/dynamic. How can I make the autofill function automatically perform in the column range lu or in the adjacent column?

I tried to write it like this

Selection.AutoFill Destination:=Range(Cells(2, Columns(lu).Column) & Range("B" & Rows.Count).End(xlUp).Row)

My current solution requires manually inputting the column Letter location every time before running the VBA

Selection.AutoFill Destination:=Range("M2:M" & Range("B" & Rows.Count).End(xlUp).Row)

Sub UpdatedSupplierListPriceVlookUp4()
'
' UpdatedSupplierListPriceVlookUp referencing local vendor PN with WF vendor pn & Supplier List Price
'
    Dim bulkWF As Worksheet
    Dim bulkRSQPMWF As Worksheet
    Dim LastRow As Long
    Dim l As Long
    Dim lu As Long
    Dim DELETEmessage As Variant

    Set bulkWF = ThisWorkbook.Sheets("WF")
    Set bulkRSQPMWF = ThisWorkbook.Sheets("RSQ PM WF")

    Sheets("RSQ PM WF").Activate

' Select Supplier List Price col for vlookup values
    l = Rows("1").Find("Supplier List Price").Column
    Columns(l).EntireColumn.Select
'Insert column to right for vlookup values
    ActiveCell.Offset(0, 1).EntireColumn.Select
    Selection.Insert Shift:=xlToRight
'insert title value = "Updated Supplier List Price"
' Set Up Column lu reference for Updated Supplier List Price Col
    ActiveCell.Offset(0, 0).Value = "Updated Supplier List Price"
    lu = Rows("1").Find("Updated Supplier List Price").Column
    ActiveCell.Offset(1, 0).Select
    
'insert VLOOKUP
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2,WF!C2:C4,3,FALSE)"
'Autofill vlookup
        Selection.AutoFill Destination:=Range("M2:M" & Range("B" & Rows.Count).End(xlUp).Row)
    
End Sub

 

[Fluff]

Try

Selection.AutoFill destination:=Cells(2, lu).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA Perform Autofill formula in specified column name/location
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

Also posted here VBA Perform Autofill formula in specified column name/location

 

[explrll]

Try

Selection.AutoFill destination:=Cells(2, lu).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1)

Hello,

Thanks so much that worked perfectly without requiring additional code line updates!

 

[Fluff]

You're welcome & thanks for the feedback.

Please note the warnings about Cross posting for the future.

 

[explrll]

CROSS POSTED ON MULTIPLE SITES
CHANDOO
OZGRID
EXCEL FORUM