CODEWITHSUNDEEP
excelvbavlookup
Akash Panchal
Akash Panchal

Reputation: 71

Run VLookup function housed within a VBA Macro Button

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:

enter image description here

enter image description here

enter image description here

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:

  1. Click VBA button
  2. runs vlookup as shown above for all values in column E Sheet1, based on the number of items to lookup in column B
  3. 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!

Upvotes: 1

Views: 1106

Answers (1)

Toddleson
Toddleson

Reputation: 4457

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.

Upvotes: 3

Related Questions