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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,702
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
74,702
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
74,702
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.

Please note the warnings about Cross posting for the future.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,963
Messages
5,856,549
Members
431,818
Latest member
Tori Murphy

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