Application defined or object defined error

Question

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

VB

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 11-Jun-19 23:17pm

divzz0194

Updated 12-Jun-19 1:13am

CHill60

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2019-06-12T01:13:00

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[^]