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