none
VLookup returns "Error 2042" when there is not match. How can I identify it pragrammaticaly?

    Question

  • Hello all,

    I am new to VB so my question probably sounds silly!

    I would like to use VLookup function but I cannot handle the return value when the function does not find the parameter. I get a "Error 2042". I dont know its actuall format. Is it only a string? I tried to check it with the Aplication.IsNA() and IsEmpty() message but they don't work correctly.

    Do you know how can I solve this problem?
    Tuesday, December 06, 2005 3:46 PM

Answers

  • Per our support engineer:

    First of all, I think our buddy asked a very good question about how to handle the exception thrown by VLOOKUP function. J


    Please ask our buddy refer to following KB article:

    How to Use VLOOKUP or HLOOKUP to find an exact match

    http://support.microsoft.com/default.aspx?scid=kb;en-us;181213


    We set the last parameter of VLOOKUP to ‘FALSE’ to find the exact matched data. We can capture the exception by calling ‘ISERROR()’ function. If ‘ISERROR()’ equals to  TRUE, it means we can not find the exact matched item in the source table. Please refer to following VB code:


    ===========================

    Dim exRange As Range

     Set exRange = Sheets("Product").UsedRange

     

     ActiveWorkbook.Names.Add Name:="ProductRange", RefersToR1C1:="=Sheet1!R1C1:R15C2"

     

     Dim currentSheet As Worksheet

     Set currentSheet = Sheets("Receipt")

     Dim i As Integer

     Dim strCmd As String, strCmd1 As String

     

     'No VLookup error handling

     For i = 2 To 4

        strCmd = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2)"

        MsgBox "Formula in Cell R" & CStr(i) & "C2: " & strCmd

        currentSheet.Range("C" & i).FormulaR1C1 = "=" & strCmd

     Next

     

     'With VLookup error handling

      For i = 6 To 8

        strCmd = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2)"

        strCmd1 = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2, False)"

       

        strCmd = "IF(ISERROR(" & strCmd1 & "),""CUSTOM ERROR""" & "," & strCmd & ")" ‘Handle the exception and replace the value in that cell with custom message

       

        MsgBox "Formula in Cell R" & CStr(i) & "C2: " & strCmd

        currentSheet.Range("C" & i).FormulaR1C1 = "=" & strCmd

     Next

    ============================

     

    Source Sheet (Product)

    ============================

    [*this is a two column table showing product and price]

    Product
     Price
     
    CPU A
     $100
     
    CPU B
     $80
     
    CPU C
     $120
     
    CPU D
     $70
     
    CPU E
     $150
     
    DDR RAM 256M
     $80
     
    DDR RAM 1G
     $200
     
    DDR RAM 512M
     $100
     
    Mainboard A
     $150
     
    Mainboard B
     $200
     
    Mainboard C
     $40
     
    Mainboard D
     $60
     
    Mainboard E
     $80
     
    Mainboard F
     $110
     

     

    Target Sheet (Receipt)

    ===========================

    [*this is a three column table showing description, product and price]

     Description
     Product
     Price
     
    CPU
     CPU A
     100
     
    RAM
     DDR RAM 256M
     200
     
    Mainboard
     Mainboard C
     40
      
     
    CPU
     CPU F
     CUSTOM ERROR
     
    RAM
     DDR RAM 512M
     100
     
    Mainboard
     Mainboard A
     150
     
    Note: ‘CPU F’ is not in the source table.

     

    -brenda (ISV Buddy Team)

    Monday, December 19, 2005 8:15 PM

All replies

  • Per our support engineer:

    First of all, I think our buddy asked a very good question about how to handle the exception thrown by VLOOKUP function. J


    Please ask our buddy refer to following KB article:

    How to Use VLOOKUP or HLOOKUP to find an exact match

    http://support.microsoft.com/default.aspx?scid=kb;en-us;181213


    We set the last parameter of VLOOKUP to ‘FALSE’ to find the exact matched data. We can capture the exception by calling ‘ISERROR()’ function. If ‘ISERROR()’ equals to  TRUE, it means we can not find the exact matched item in the source table. Please refer to following VB code:


    ===========================

    Dim exRange As Range

     Set exRange = Sheets("Product").UsedRange

     

     ActiveWorkbook.Names.Add Name:="ProductRange", RefersToR1C1:="=Sheet1!R1C1:R15C2"

     

     Dim currentSheet As Worksheet

     Set currentSheet = Sheets("Receipt")

     Dim i As Integer

     Dim strCmd As String, strCmd1 As String

     

     'No VLookup error handling

     For i = 2 To 4

        strCmd = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2)"

        MsgBox "Formula in Cell R" & CStr(i) & "C2: " & strCmd

        currentSheet.Range("C" & i).FormulaR1C1 = "=" & strCmd

     Next

     

     'With VLookup error handling

      For i = 6 To 8

        strCmd = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2)"

        strCmd1 = "VLOOKUP(" & "R" & CStr(i) & "C2,Product!R1C1:R15C2,2, False)"

       

        strCmd = "IF(ISERROR(" & strCmd1 & "),""CUSTOM ERROR""" & "," & strCmd & ")" ‘Handle the exception and replace the value in that cell with custom message

       

        MsgBox "Formula in Cell R" & CStr(i) & "C2: " & strCmd

        currentSheet.Range("C" & i).FormulaR1C1 = "=" & strCmd

     Next

    ============================

     

    Source Sheet (Product)

    ============================

    [*this is a two column table showing product and price]

    Product
     Price
     
    CPU A
     $100
     
    CPU B
     $80
     
    CPU C
     $120
     
    CPU D
     $70
     
    CPU E
     $150
     
    DDR RAM 256M
     $80
     
    DDR RAM 1G
     $200
     
    DDR RAM 512M
     $100
     
    Mainboard A
     $150
     
    Mainboard B
     $200
     
    Mainboard C
     $40
     
    Mainboard D
     $60
     
    Mainboard E
     $80
     
    Mainboard F
     $110
     

     

    Target Sheet (Receipt)

    ===========================

    [*this is a three column table showing description, product and price]

     Description
     Product
     Price
     
    CPU
     CPU A
     100
     
    RAM
     DDR RAM 256M
     200
     
    Mainboard
     Mainboard C
     40
      
     
    CPU
     CPU F
     CUSTOM ERROR
     
    RAM
     DDR RAM 512M
     100
     
    Mainboard
     Mainboard A
     150
     
    Note: ‘CPU F’ is not in the source table.

     

    -brenda (ISV Buddy Team)

    Monday, December 19, 2005 8:15 PM
  • I was having some trouble when trying to get the length of the cell because it had the #NA value.

    Len(Cells(intSourceRow, intSourceColumn)) = 0

    I used it inside and If command, then what I did was

    To prevent the error I select the value and change it for another one located in the cell (1,1).

     Cells(intSourceRow, intSourceColumn + 1).Select

        If IsError(Selection) Then
                Cells(1, 1).Select
                Selection.Copy
                Cells(intSourceRow, intSourceColumn + 1).Select
                Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
             End If

    DRUDEM

    Thursday, December 20, 2012 6:33 PM