Multiple Vlookups in one macro VBA

Gary Lens
Gary Lens used Ask the Experts™
on
I’m attempting to write a macro that will have 6 vlookups in one statement but I’m having an error message.

The below macro works when it’s only two vlookups but I would want to add additional vlookups in the macro below. Can you please assist? Thanks in advance.


The other references I want to look up are below from Sheet2;

G:H
J:K
M:N
P:Q


Example

Sub test()
Dim LastRow As Long
With Sheets(“sheet1”)
        LastRow =.Cells(.Rows.Count, “A”).End(xlUp).Row
        With.Range(“B2:B” & LastRow)
               .Formula =“=IFERROR(VLOOKUP(A2,Sheet2!$A:$B,2,0),IFERROR(VLOOKUP(A2,Sheet2!$D:$E,2,0),””””))”
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
As far as I am aware max nested functions allowed are 7.
Why not utilize the Find method to achieve the same output like below? Tweak it if required.

Sub test()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long
Dim LookupRng As Range, Rng As Range, cell As Range

Application.ScreenUpdating = False
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
Set LookupRng = Union(ws2.Range("A:A"), ws2.Range("D:D"), ws2.Range("G:G"), ws2.Range("J:J"), ws2.Range("M:M"), ws2.Range("P:P"))

LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In ws1.Range("A2:A" & LastRow)
    With LookupRng
        Set Rng = .Find(what:=cell.Value, after:=ws2.Range("A1"), lookat:=xlWhole)
        If Not Rng Is Nothing Then
            cell.Offset(0, 1) = Rng.Offset(0, 1)
        End If
    End With
Next cell
Application.ScreenUpdating = True
End Sub

Open in new window

Gary LensProject Manager

Author

Commented:
How can I change the output cell from B1 to R1?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
For that, replace the line#17
cell.Offset(0, 1) = Rng.Offset(0, 1)

Open in new window

WITH
ws1.Cells(cell.Row, "R") = Rng.Offset(0, 1)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Gary LensProject Manager

Author

Commented:
Thank you but when I changed that line code, now it doesn't go to the last row..it only does the first row.
How can I have it to go to the last row..
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
I have logged off and on mobile right now.
BTW even you make the suggested change, rhe lookup values are still in column A. The new line will only change the output column from B to R.
Also, if the code is only working for one row, check in which column you are trying to find the LastRow.
Gary LensProject Manager

Author

Commented:
This worked perfectly. I was able to edit a couple lines but this got me going in the right direction.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad it worked as desired in the end.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial