Run VLookup function housed within a VBA Macro Button

Question

I've been working on a Vlookup function (that works within a cell) as shown below:

=IFERROR(VLOOKUP(B2&"*", 'Sheet2'!$AC:$AC, 1, FALSE), "N/A")

This is the whole idea behind using this formula:

lookup the text string value in Sheet1 col.B (example: BajaTurkeyAvocado_6S_HeroRegular_Mobile)

see if a value similar to this exists in Sheet2 col. AC (example: BajaTurkeyAvocado_6S_HeroRegular_Mobile.png is a match since it's a similar value)

If it's a match, place Sheet2 col. AC value into Sheet1 col. E (This column holds my VLookup function, shown above):

I'm having a hard time implementing this VLookup function within my VBA Macro button. I don't want to have it written within the cells in column E.

This is what my goal is:

Click VBA button
runs vlookup as shown above for all values in column E Sheet1, based on the number of items to lookup in column B
Converts the lookup values into string in column E (rather than formula)

In short, just knowing how to convert a vlookup function written in a cell to a function written in a VBA macro button would be very helpful.

Thank you!

user_14608750 · Answer

Thankfully, VBA automatically adjusts formulas when you apply them to a multi-cell range. So you can directly apply that formula you created to the entire column at once and not have to loop.

Then you can just do .Value = .Value to convert the formulas into strings.

Sub Example()
    Dim lastrow As Long
    lastrow = Sheet1.Cells(Sheet1.Rows.Count, 2).End(xlUp).Row
    
    Dim TargetRange As Range
    Set TargetRange = Sheet1.Range("E2:E" & lastrow)
    With TargetRange
        .Formula = "=IFERROR(VLOOKUP(B2&""*"", 'Sheet2'!$AC:$AC, 1, FALSE), ""N/A"")"
        .Value = .Value
    End With
End Sub

LastRow is used to trim the range to the used area since VLookUp is expensive and running it on an entire column (even the empty parts) will waste a lot of time.

Run VLookup function housed within a VBA Macro Button

1 Answers