MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Vlookup in vba when lookup fails

  Asked By: Ryan    Date: Jan 15    Category: MS Office    Views: 2415

Vlookup which gives iserror(Vlookup(......)) in excel rformula. where i can
write, if else statements, so that when there is error since no match than the
value written can be FALSE.

However i want to do this is excel vba.
Check the application if there is an error for vlookup than do something. But
in vba i cannot use iserror() so How should i solve it



2 Answers Found

Answer #1    Answered By: Tracy Myers     Answered On: Jan 15

You may need a more advanced Excel guru to help out from this thread, as
I wouldn't even use the vLookup function. I would build my criteria all
within VBA.

I would grab my criteria, then move left or right, looking for my
information. Then I can use an if\else statement based on what I found
or not.

Answer #2    Answered By: Vonda Ramirez     Answered On: Jan 15

In vba  something along these lines?:

Sub sequently()
On error  GoTo gonewrong
Debug.Print Application.WorksheetFunction. _
VLookup(Range("G10"), Range("D10:E17"), 2, False)
On Error GoTo 0
Exit Sub
MsgBox "yuk!"
End Sub

The On Error statement applied just before the vlookup  statement, then
cancelled straight after that statement with On Error GoTo 0 (Actually
unnecessary above as the vlookup statement is the only one and On
Error statements  are cancelled on exit from the procedure they were
used in!).
In this case the On Error statement, when met by any error, will
divert to the code after the label 'gonewrong'.
To prevent that portion of code being executed inappropriately (when
there is no error), the Exit Sub line is inserted just before it.

ps Debug.Print prints to the immediate window in the VBE; Ctrl+G in
the VBE if you can't see it.

Didn't find what you were looking for? Find more on Vlookup in vba when lookup fails Or get search suggestion and latest updates.