Click here to Skip to main content
14,735,744 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
the following code, takes the value from the cell which is separated by comma
those values separated by comma needs to be checked for availability in "Curve mapping sheet")

vlookup is not working and throwing the error application defined or object defined error
sub sample()

Dim R1
R1 = Worksheets("Plain Vanilla Swap Details (CC)").Range("AP2").value

 If R1 <> "" Then
                rates = Split(R1, ",")
                Final = UBound(rates)                
                For i = 0 To Final

                Message = (rates(i))                    
V1 = WorksheetFunction.VLookup(Message, Worksheets("Curve Mapping").Range("D2:H209"), 2, False)

                    Next i

                    End If

End Sub

What I have tried:

I tried by explicitly typing the value in vlookup statement, then the code is working fine.. pls help
Updated 12-Jun-19 2:13am

1 solution

Instead of using
That way the exception when it can't find the value (Error 2042) will not be thrown. You will need to check the result for the error though
if IsError(v1) then

You can have issues using VLOOKUP if the data is not the same type.
For example I am still getting an error (although it is handled better), but if I use
message = [an exact value from the range]
I don't get the error.
So you need to make sure that the data in your Lookup Range is of the same type as the lookup value. It's always a lot easier to use text for your lookups than numeric or date.

Basically both your original exception and my suggested solution are reporting that they cannot find the value you are trying to look up.

The following link might help you sort out the VLOOKUP How to correct a #N/A error in the VLOOKUP function - Office Support[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900