VBA Vlookup between two workbooks

jscranton

Well-known Member
Joined
May 30, 2011
Messages
707
For some reason I am getting an #N/A error (2042) for this Vlookup.

The lookup range is another workbook so I think the error is somewhere in how I am referring to the srchRange.

Any ideas?

Code:
Sub checkThis()
Dim lookFor As Variant
Dim srchRange As Range
Dim MacroBook As Workbook
Dim startRow, endRow
Dim ws As Worksheet
Dim empLocation As Variant
 
ThisWorkbook.Activate
 
Set ws = ThisWorkbook.ActiveSheet
 
startRow = 1
endRow = 2
 
For I = startRow To endRow - 1  'lookup employee location
 
'On Error Resume Next


    Set MacroBook = Workbooks("Workbook2.xlsx")
  
    lookFor = Trim(ws.Range("A" & I).Value)
       
    Set srchRange = MacroBook.Sheets("Sheet2").Range("A:B")


    empLocation = Application.VLookup(lookFor, srchRange, 2, False)


    Debug.Print empLocation
    Next I


End Sub
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Vlookup is not a function of application.

Application.WorksheetFunctions.Vlookup is what you're after.

(I think)
 
Upvote 0
I tried that originally. Object doesn't support method.

empLocation = Application.WorksheetFunctions.VLookup(lookFor, srchRange, 2, False)
 
Upvote 0
Is the other workbook open when you get the #N/A? Have you verified that the name you are searching for in the other workbook is there and has no extra spaces or unseen characters - i.e. is exactly what you have in lookFor?
 
Upvote 0
I tried that originally. Object doesn't support method.

empLocation = Application.WorksheetFunctions.VLookup(lookFor, srchRange, 2, False)


Ok, so I'm guessing your issue is your lookup value lookFor. Vlookup is annoying about what it will return. If you have an integer as number format as your lookup value, and you want to lookup into a table where it is text? No bueno. So I would probably cast your lookFor as a string. I don't know if that will help.

I tested the below code with "PANCAKE" as the lookup value.


Code:
Sub checkBlooks()

Dim startRow, endRow
Dim ws As Worksheet
Dim empLocation As Variant
 
ThisWorkbook.Activate
 
Set ws = ThisWorkbook.ActiveSheet
 
startRow = 1
endRow = 2
 
For I = startRow To endRow - 1  'lookup employee location
 
'On Error Resume Next




    Debug.Print lookFor


    With Workbooks("Book20").Sheets("Sheet2")
        empLocation = Application.VLookup(CStr(Trim(ws.Range("A" & I).Value)), .Range("A1:B100"), 2, False)
    End With


    Debug.Print empLocation
Next I






End Sub


And it returns the expected value.
 
Upvote 0
Not working for me. I converted the number values to strings and I even tested it with the known value in the range and it matches.

So this returns yes:

Code:
      If lookFor = TargetBook.Sheets(2).Range("B212").Value Then
            Debug.Print "Yes"
        End If

But this returns an Error 2042:

Code:
   With TargeBook.Sheets(2)
        empLocation = Application.VLookup(CStr(lookFor), .Range("B212:C212", 2, False)
   End With
 
Upvote 0
I don't know if this is it, but you have a typo. You spelled "TargeBook" instead of "TargetBook". If that doesn't solve it, we can look at writing a looping offset formula. It's a little more cpu heavy but if your excel hates vlookup, what choice is there.
 
Upvote 0
Thanks. That's not it. I assume I could just create an array out of the range and do something like this:

Code:
Dim arr() as Variant

arr() = TargetBook.Sheets(2).Range("B2:C500")

    For x = LBOUND(arr) to UBOUND(arr) 

               if arr(x,1) = lookFor then
                 
                    empLocation = arr(x,2)
                    Exit For
               End IF
 Next x

Is that what you had in mind?
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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
Back
Top