Solved: vlookup error 2042

[Ago]

i use a vlookup to find a match on a different sheet with this code:

result = Application.VLookup(str, Sheets(3).Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
MsgBox result

and that works just fine while it finds something, but in 99,99% of the times it will not find anything.
that makes the result become "error 2042" and causing it to crash.

how can i avoid the msgbox-code when result is 2042?

i have tried

if result = "error 2042"
on error goto 1:
msgbox result
1:
if is not err.number = 2042
:banghead:
its probably simple, what did i do wrong?

[mdmackillop]

On Error Resume Next
Result = Application.VLookup(MyStr, Sheets(3).Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
If Result = "Error 2042" Then Result = "Nothing Found"
MsgBox Result
On Error GoTo 0

[nst1107]

TryOn Error Resume Next

[lucas]

There are times when it is acceptable to use On error resume next.....this seems to fit that need.

You have to be careful using it though as in the wrong usage it will ignore errors that can cause incorrect results....

[Ago]

oh sorry i forgott to say i dont like on error resume next since this might make the code skip by a serious thing.

if result= "Error 2042"
gives me an errorcode 13 incompatible types.
i think this is because the Error 2042 is not a string, its something else.
it looks like a string when i hower the mousepointer above it, but its not.

[Ago]

There are times when it is acceptable to use On error resume next.....this seems to fit that need.

You have to be careful using it though as in the wrong usage it will ignore errors that can cause incorrect results....

yeah.. i was typing my other reply while you wrote this.

the code i have is much more complex than this part i showed here, so a resume next is very risky in my opinion.

edit:

is there a way to say:

use on error resume next from here...
....

....
...
.....
...

to here.

?

[mdmackillop]

Post 2 restores normal error operation

[Ago]

you mean on error goto 0?

sweet! thanks that could work

[mdmackillop]

BTW, avoid using Str as a variable. It is a VBA function. I used MyStr in my example.

[lucas]

I was wondering if you had read past Malcolms suggestion......

[Ago]

lucas, yes i did read his post but since it "wont work".

if result = "Error 2042"
that crashes since its incompatible types.

and even if it would work it would change result to "Nothing found"
then do msgbox result.
that mean i would get a msgbox "all the time" with nothing found.
not really what i was hoping on.

but i had no clue about on error goto 0 means restore "default procedure".

mdmacillop: thanks i had no idea that was a function. i will change that!
i just made this code as a "test" since i wanted to make the code run faster.
i used to have a loop doing the same thing, but as the list gets bigger the code will run slower so i figured i would take care of the issue before it becomes a problem.

thanks alot guys!!

[kobusdutoit]

i use a vlookup to find a match on a different sheet with this code:

result = Application.VLookup(str, Sheets(3).Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
MsgBox result

and that works just fine while it finds something, but in 99,99% of the times it will not find anything.
that makes the result become "error 2042" and causing it to crash.

how can i avoid the msgbox-code when result is 2042?

i have tried

if result = "error 2042"
on error goto 1:
msgbox result
1:
if is not err.number = 2042
:banghead:
its probably simple, what did i do wrong?


Albeit an old thread, I recently had to do some vba excel work and came across this same issue. I'm not a fan of using "on error resume next" so i followed this solution:


dim vresult as variant
vresult = Application.VLookup(str, Sheets(3).Range("A1:B" & Cells(Rows.Count, 1).End(xlUp).Row), 2, False)
If IsError(vresult ) Then
' do what you need to do
else
' do something else
End If

Hope this helps someone in future...

[SamT]

Kobudotoit,

Welcome to VBA Express, and thanks for the alternate solution. I'm sure someone will find it handy