VBA Perform Autofill formula in specified column name/location

explrll

New Member
Joined
Apr 6, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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
VBA Code:
Selection.AutoFill Destination:=Range("M2:M" & Range("B" & Rows.Count).End(xlUp).Row)

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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
Try
VBA Code:
Selection.AutoFill destination:=Cells(2, lu).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
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.
 

explrll

New Member
Joined
Apr 6, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,850
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

Please note the warnings about Cross posting for the future.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,167
Members
417,128
Latest member
Xianter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top