VBA VLookup Error

M

Michael1974

New member
Joined
Mar 31, 2015
Messages
49
Reaction score
0
Points
0
Good morning Jaslake,

I have 2 tables on the attached spreadsheet (1 table per sheet). My goal is to use the VBA VLookup to verify if the item # in the 2 tables are the same.

After running the macro, I have as a result #N/A for every Item #. This should mean that there is no match of Item # between the 2 tables.

But after checking visually, it does appear that we do have some matches. Question: How come this match does not appear when we run the macro?

Also sometimes, instead of having the information from the column N of sheet 2, I have the information from Column N of sheet 1 that appears after running the macro.

Thanks in advance for your help!
 

Attachments

  • VLookUp Issues.zip
    628.3 KB · Views: 25
I couldn't see any that matched, so all would be #N/A.

To fix the sheet problem, use

Code: 
        Range("D2:D" & lastRow).Formula = "=VLOOKUP(TRIM(A2), '" & Sheet2.Name & "'!" & Table2.Address & ",14,False)"
 
Last edited:
Bobe,

Thank you for trying to help but it still does not work. We do have some values that match like Item #
015619595 or
014775227 But they do not appear when you run the macro.
 
There are no items like that on the lookup sheet, they all start with CASREP, CSMP or MAINT ITTEM.
 
Bobe,

My goal is to compare column A from sheet 1 called Item # with Column N of sheet 2 called Item # as well. Basically the LookUp Value is in Column A of sheet 1 and the lookup Array is the table in Sheet2
 
Jaslake or anyone in this forum, does anybody have an explanation on why the lookup does not work?
 
Hi Michael

See if this does as you require
Code: 
Option Explicit
Sub LookUp()
   'put all the variable definitions at top for easy reference
   Dim Table1       As Range
   Dim Table2       As Range
   Dim lastRow      As Long
   Dim lastrow2     As Long
   Application.ScreenUpdating = False
   lastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
   lastrow2 = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
   Set Table1 = Sheet1.Range("A1:O65536")   ' Employee_ID Column from Employee table
   Set Table2 = Sheet2.Range("N1:N65536")   ' Range of Employee Table 1
   With Sheet1.Range("D2")
      Range("D2:D" & lastRow).Formula = "=VLOOKUP(TRIM(A2), '" & Sheet2.Name & "'!" & Table2.Address & ",1,False)"
      Range("D2:D" & lastRow).Copy
      Range("D2:D" & lastRow).PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
   MsgBox "Done"
End Sub
 
Good morning Jaslake,

Thank you for your effort to help. I have tried the code. When I compare the items # in sheet 1 that should have a match with the ones in sheet 2, many items # in sheet 1 for which we are supposed to have a match in sheet 2 do not appear in sheet2. Any idea why?

Thanks
 
Jaslake,

For example if you take those items numbers below in sheet 1 and look for them in sheet 2 (column N), I did not find them in sheet 2 while after running the macro, it shows that there is a match. Please look at the item numbers below in sheet 1 (Column A) and look for them in sheet 2 Column N):

015767803, 015628451, 123604062, 123479418

Thanks
 
Jaslake,

Thank you anyway. Please let me know if you have another code that can get rid of this issue.

Thanks
 
Hi Michael

I believe you should be looking in Sheet5, not Sheet2
Code: 
Option Explicit
Sub LookUp()
   'put all the variable definitions at top for easy reference
   Dim Table1       As Range
   Dim Table2       As Range
   Dim lastRow      As Long
   Dim lastrow2     As Long
   Application.ScreenUpdating = False
   lastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
   lastrow2 = Sheet5.Cells(Rows.Count, "B").End(xlUp).Row
   Set Table1 = Sheet1.Range("A1:O65536")   ' Employee_ID Column from Employee table
   Set Table2 = Sheet5.Range("N1:N65536")   ' Range of Employee Table 1
   With Sheet1.Range("D2")
      Range("D2:D" & lastRow).Formula = "=VLOOKUP(TRIM(A2), '" & Sheet5.Name & "'!" & Table2.Address & ",1,False)"
      Range("D2:D" & lastRow).Copy
      Range("D2:D" & lastRow).PasteSpecial xlPasteValues
   End With
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
   MsgBox "Done"
End Sub
 
Good morning Jaslake,

But the first code that you sent to me was the one below. Can you please upload the worksheet for me? Thanks. Please see below the code that you sent to me first.

Option Explicit
Sub LookUp()
'put all the variable definitions at top for easy reference
Dim Table1 As Range
Dim Table2 As Range
Dim lastRow As Long
Dim lastrow2 As Long
Application.ScreenUpdating = False
lastRow = Sheet1.Cells(Rows.Count, "B").End(xlUp).Row
lastrow2 = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
Set Table1 = Sheet1.Range("A1:O65536") ' Employee_ID Column from Employee table
Set Table2 = Sheet2.Range("N1:N65536") ' Range of Employee Table 1
With Sheet1.Range("D2")
Range("D2" & lastRow).Formula = "=VLOOKUP(TRIM(A2), '" & Sheet2.Name & "'!" & Table2.Address & ",1,False)"
Range("D2" & lastRow).Copy
Range("D2" & lastRow).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Done"
End Sub
 
Jaslake,

From the sheet that I have, I do not have any sheet5. Please send me your sheet in question in order for me to see what you are talking about.

Thanks
 
Hi Michael

The Code Name for Sheet2 is Sheet5
attachment.php
 

Attachments

  • 2015-04-14_13-41-29.jpg
    2015-04-14_13-41-29.jpg
    52.4 KB · Views: 25
  • VlookUp issue.zip
    639.9 KB · Views: 6
Jaslake,

I think that I understand what you meant now and it seems to work. You are a life savior! Thank you very very much!!!! Just one question, can you please explain to me this code:

"=VLOOKUP(TRIM(A2), '" & Sheet5.Name & "'!" & Table2.Address & ",1,False)"

I know the readitional worksheetfunction VLookUp in Excel. But what I don't understand is why put this additional line besides Table2.Address: '" & Sheet5.Name & "'!" & ?

Thanks
 
Hi Michael

Comment out these two lines of Code then run the Code.
Code: 
 Range("D2:D" & lastRow).Copy
 Range("D2:D" & lastRow).PasteSpecial xlPasteValues
Now look in Column D of Sheet1...this is what it translates to...understand?
 
Jaslake,

Apparently my question was not clear, I was just asking for this portion of the code: '" & Sheet5.Name & "'!" & ?

Why add this to the original code?

Thanks
 
Hi Michael

This is the Code
Code: 
=VLOOKUP(TRIM(A2), '" & Sheet5.Name & "'!" & Table2.Address & ",1,False)"

This is the result of the Code...
Code: 
=VLOOKUP(TRIM(A2), Sheet2!$N:$N,1,FALSE)

Think about it...then, you explain it to me...
 
You must log in or register to reply here.
Back
Top