CODEWITHSUNDEEP
vbaexcel
Emily Alden
Emily Alden

Reputation: 570

Vlookup returning error when item is in the list

Issue: Item is in VLOOKUP table, but is returning the value of "Missing" in the appropriate cell. When conUD prints if I copy paste it from the Debug window and Ctrl+F - find it on the J column it finds it without issue. Why would it be found by Ctrl+F and not VLookup?

Notes: 

Option Explicit
Dim wsMain As Worksheet
Dim wsQuantity As Worksheet
Dim wsVelocity As Worksheet
Dim wsParameters As Worksheet
Dim wsData As Worksheet
Dim lrMain As Long 'lr = last row
Dim lrQuantity As Long
Dim lrVelocity As Long
Dim lrParameters As Long
Dim lrData As Long
Dim conUD As String 'con=concatenate
Dim conECD As String
Dim calcWeek As Long
Dim RC As Long 'Row Counter
Dim vl As Variant 'Vlookup, Variant to allow for errors without breaking the code

calcWeek = wsParameters.Range("B3").Value
lrVelocity = wsVelocity.Cells.Find(What:="*", SearchOrder:=xlByRows, Searchdirection:=xlPrevious).Row
Set wsMain = Worksheets("Main Tab")
Set wsVelocity = Worksheets("Velocity")

 For RC = 2 To 10 'lrVelocity
With wsVelocity
    .Cells(RC, 10) = .Cells(RC, 1) & .Cells(RC, 1) & .Cells(RC, 4) & .Cells(RC, 5) & .Cells(RC, 9)
    .Cells(RC, 10).Value = CStr(Trim(.Cells(RC, 10).Value))
    .Cells(RC, 11) = .Cells(RC, 6)
    .Cells(RC, 12) = .Cells(RC, 7)
    .Cells(RC, 13) = .Cells(RC, 8)
    .Cells(RC, 14) = .Cells(RC, 3)
    .Cells(RC, 22) = .Cells(RC, 1) & .Cells(RC, 9)
End With
Next RC

For RC=2 To 10
conUD = wsMain.Cells(RC, 21) & wsMain.Cells(RC, 4) & calcWeek
conUD = CStr(Trim(conUD))
Debug.Print conUD
wsVelocity.Activate
vl = Application.VLookup(conUD, wsVelocity.Range(wsVelocity.Cells(2, 10), wsVelocity.Cells(lrVelocity, 11)), 2, False)
If IsError(vl) Then
    wsMain.Cells(RC, 10).Value = "Missing"
Else
    wsMain.Cells(RC, 10).Value = vl
End If
Next RC

Upvotes: 2

Views: 241

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I think @user1274820 is on to something. Normally we use Application.Vlookup as you're using, with the anticipation that perhaps the value will not be found in the first column of table_array, and you want to handle that with "Missing" value in the output.

HOWEVER, if the value is found, but the value in the return column (k, in your case) is an error, then the function will return the error as well. In your case, while the value is found in column J, it would seem that column K contains an #N/A. (Let me know if this is not the case!)

Application.Vlookup returns an Error 2042 in both of these cases:

  1. lookup_value is not found in the first column of table_array (most common usage & expectation, IMO)
  2. lookup_value IS found, but the resulting value from col_index_num is itself an error.

So, if the return value might contain an error even if the lookup value exists, then we can't use Application.Vlookup to test for the presence of the value, but you can use an alternate method like WorksheetFunction.CountIf or Application.Match.

Here, we simply query the column J and use CountIf to ensure there's at least 1 matching value. This will validate our Vlookup in advance, but we still need to handle the possible error in the return value.

For RC = 2 to 10
    conUD = wsMain.Cells(RC, 21) & wsMain.Cells(RC, 4) & calcWeek
    conUD = CStr(Trim(conUD))
    Debug.Print conUD
    With wsVelocity
        Dim lookupRange as Range
        Set lookupRange = .Range(.Cells(2, 10), .Cells(lrVelocity, 11))
    End With
    If Application.WorksheetFunction.CountIf(lookupRange.Columns(1), conUD) <> 0 Then
        'The value is found, it should be safe to use VLOOKUP 
        vl = Application.VLookup(conUD, lookupRange, 2, False)
        '## Handles an error in the return value from the return column
        If IsError(vl) Then
            '## Copies the error from return column, or modify as needed
            wsMain.Cells(RC, 10).Value = CVerr(vl)
        Else
            '## Value found in Col J and return Vlookup from Col K
            wsMain.Cells(RC, 10).Value = vl
        End If
    Else
        '## Value NOT FOUND in column J
        wsMain.Cells(RC, 10).Value = "Missing"
    End If
Next

Update

From chat, I can see the formatting of your Main and Lookup tables values is different. In your lookup table, you're duplicating a prefix e.g., "0001HCM8889" and so you end up with "0001HCM8890001HCM889W01".

This is why Find or Ctrl+F will find the cell, but VLOOKUP won't, because it's requiring an exact match.

As it appears you're constructing/sanitizing the lookup table in your first loop, you should be able to fix it by doing this:

For RC = 2 To 10 'lrVelocity

    With wsVelocity
        '## Removed the duplicate .Cells(RC, 1) from the next line ##
        .Cells(RC, 10) = .Cells(RC, 1) & .Cells(RC, 4) & .Cells(RC, 5) & .Cells(RC, 9)
        .Cells(RC, 10).Value = CStr(Trim(.Cells(RC, 10).Value))
        .Cells(RC, 11) = .Cells(RC, 6)
        .Cells(RC, 12) = .Cells(RC, 7)
        .Cells(RC, 13) = .Cells(RC, 8)
        .Cells(RC, 14) = .Cells(RC, 3)
        .Cells(RC, 22) = .Cells(RC, 1) & .Cells(RC, 9)
    End With

Next RC

Upvotes: 1

Related Questions