VBA Perform Autofill formula in specified column name/location

explrll

New Member
Joined
Apr 6, 2021
Messages
4
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try
VBA Code:
Selection.AutoFill destination:=Cells(2, lu).Resize(Range("B" & Rows.Count).End(xlUp).Row - 1)
 
Upvote 0
Solution
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.
 
Upvote 0
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!
 
Upvote 0
You're welcome & thanks for the feedback.

Please note the warnings about Cross posting for the future.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

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
Back
Top