VBA Perform Autofill formula in specified column name/location

explrll · Apr 6th 2021

[xpost]

[/xpost]

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 with 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

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

Code

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

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

Display More

**Carim** · Apr 6th 2021

Hello and Welcome to the Forum

You could test the following

Code

Sub UpdatedSupplierListPriceVlookUp5()
' UpdatedSupplierListPriceVlookUp referencing local vendor PN with WF vendor pn & Supplier List Price
 Dim bulkWF As Worksheet, bulkRSQPMWF As Worksheet
 Dim lg As Long
 
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
 lg = bulkRSQPMWF.Rows("1").Find("Supplier List Price").Column
'Insert column to right for vlookup values
    With bulkRSQPMWF
        .Columns(lg + 1).EntireColumn.Insert Shift:=xlToRight
        .Cells(1, lg + 1) = "Updated Supplier List Price"
        .Cells(2, lg + 1).FormulaR1C1 = "=VLOOKUP(RC2,WF!C2:C4,3,FALSE)"
        .Cells(2, lg + 1).Copy .Range(Cells(3, lg + 1), Cells(Range("B" & Rows.Count).End(xlUp).Row, lg + 1))
    End With
End Sub

Display More

Hope this will help

**Carim** · Apr 6th 2021

Thanks to Fluff 13 for posting CROSS POST

Fluff13 18 minutes ago

X posted

mrexcel.com/board/threads/vba-…mn-name-location.1167262/

amongst other sites

explrll · Apr 6th 2021

CROSS POSTED ON:

chandoo.org

https://chandoo.org/forum/thre…lumn-name-location.46057/

EXCEL FORUM

https://www.excelforum.com/exc…location.html#post5499027

**Carim** · Apr 6th 2021

Thanks for listing all your CROSS POSTS ...

Thanks also for the Like

Does it mean the macro is producing the expected result ...???

explrll · Apr 6th 2021

Quote from Carim

Thanks for listing all your CROSS POSTS ...

Thanks also for the Like

Does it mean the macro is producing the expected result ...???

Hello,

Yes, your code did produce the correct results. However, I will use a different solution which was simpler because it only changed 1 line of code

Code

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

**Carim** · Apr 6th 2021

Glad you could fix your problem

You will gradually learn that using Select and Selection in your macros is counter-productive ...

1. It is the result of the macro recorder which is only a starting point ...

2. and, it has a negative impact on the overall performance of your workbook ...

VBA Perform Autofill formula in specified column name/location

Fluff13 18 minutes ago

Participate now!

Share

Tags