Click here to Skip to main content
14,209,923 members
Rate this:
Please Sign up or sign in to vote.
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
Posted
Updated 12-Jun-19 1:13am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Instead of using
WorksheetFunction.VLookup(...
use
Application.VLookup(...
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)



Advertise | Privacy | Cookies | Terms of Service
Web03 | 2.8.190617.3 | Last Updated 12 Jun 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100