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

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


On Jun 16, 11:16*am, Cornelius
wrote:
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

Thank you Paul!



" wrote:

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


On Jun 16, 11:16 am, Cornelius
wrote:
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

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
--
jb


"Cornelius" wrote:

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

Thank you John!




"john" wrote:

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
--
jb


"Cornelius" wrote:

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

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)




"Cornelius" wrote:

Thank you John!




"john" wrote:

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
--
jb


"Cornelius" wrote:

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

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

"Cornelius" wrote:

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)




"Cornelius" wrote:

Thank you John!




"john" wrote:

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
--
jb


"Cornelius" wrote:

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