Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: VBA VLookup Error

  1. #1

    VBA VLookup Error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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!
    Attached Files Attached Files

  2. #2
    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 by Bob Phillips; 2015-04-09 at 03:37 PM.

  3. #3
    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.

  4. #4
    There are no items like that on the lookup sheet, they all start with CASREP, CSMP or MAINT ITTEM.

  5. #5
    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

  6. #6
    Jaslake or anyone in this forum, does anybody have an explanation on why the lookup does not work?

  7. #7
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    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
    John

  8. #8
    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

  9. #9
    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

  10. #10
    Acolyte jaslake's Avatar
    Join Date
    Aug 2011
    Location
    mineral city oh usa
    Posts
    81
    Articles
    0
    Excel Version
    2007;2010;MAc2011
    No idea Michael...sorry.
    John

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •