Vlookup - error handling with VBA

C

Cornelius

Hi,

Im working on a spreadsheet where I use Vlookup to find a specific value. I
need a code for handling search values that does not exists in the range.
Instead of the standard error code, I want to display a MsgBox with
information that the search string was not found in the range.

Can someone help med with an If sentence?

- Cornelius
 
P

paul.robinson

Hi
I tend to use

err.clear
on error resume next

' vlookup code
if err.number<>0 then
msgbox "LookUp Error"
end if

on error goto 0

The err method lasts for the session, so if you are doing VLookUp in a
loop, you must clear it each time inside the loop as well as before
the loop.

regards
Paul
 
J

john

not something I use but think you could use this approach:

myname = "test"
Set lookuprng = Worksheets("Sheet1").Range("A:C")
res = Application.VLookup(myname, lookuprng, 1, False)
If IsError(res) = False Then
MsgBox res
Else
MsgBox (myname & " Not Found")
End If
 
C

Cornelius

Thank you John!




john said:
not something I use but think you could use this approach:

myname = "test"
Set lookuprng = Worksheets("Sheet1").Range("A:C")
res = Application.VLookup(myname, lookuprng, 1, False)
If IsError(res) = False Then
MsgBox res
Else
MsgBox (myname & " Not Found")
End If
 
C

Cornelius

Thank you Paul!



Hi
I tend to use

err.clear
on error resume next

' vlookup code
if err.number<>0 then
msgbox "LookUp Error"
end if

on error goto 0

The err method lasts for the session, so if you are doing VLookUp in a
loop, you must clear it each time inside the loop as well as before
the loop.

regards
Paul
 
C

Cornelius

hi;
i have the same problem and I guess you already explained the solution.
Unfortunately I have no idea of vba.

I went to Microsoft Visual Basic and put in this

myname = "test"
Set lookuprng = Worksheets("Sheet1").Range("A:C")
res = Application.VLookup(myname, lookuprng, 1, False)
If IsError(res) = False Then
MsgBox res
Else
MsgBox (myname & " Not Found")
End If


"myname" is how I want to name the formula right?
what elso do have to change to get it working? And what do I type in in
excel once i finished this?

=myname(?)

Corneluis
(didnot know that my name is so commen)
 
C

Cornelius

Hi Cornelius,

If you want to use VBA you can try this code. It will display an input box
where you should enter the search value. The corresponding value will then be
returned in a message box. I ended up using the error handling from Paul.

Sub Vlookup()

Dim Search As String
Dim Result As String

'Specify the search range
Set lookuprng = Worksheets("Sheet1").Range("A:C")


'Ask for Search value
Search = InputBox("Insert Search value")
'Error handling if user click cancel
If Search = "" Then
Exit Sub
End If

On Error Resume Next
Result = WorksheetFunction.Vlookup(Search, Range("A:C"), 2, False)

'Error handling if search string is not found
If Err.Number <> 0 Then
MsgBox Search & " not found"
Exit Sub
End If
On Error GoTo 0

MsgBox "Value found: " & Result

End Sub

Hope this will help you:)

- Cornelius
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top