Msgbox if #N/A error

[redspanna]

Hello all

I have a button which when selected executes a VLOOKUP formula.
The result from this VLOOKUP formula is then copy/pasted elsewhere in my worksheet.

I would like a Msgbox to be displayed showing an error if the results of the VLOOKUP is #N/A, in other words if the searched value can't be found ...

Help much appreciated

 

[Gary's Student]

Here is a very simple example of code that either pastes the result or issues an error message:

Sub sample()
  Dim v As Variant
  On Error GoTo kapture
  v = Application.WorksheetFunction.VLookup(Range("A1").Value, Range("B1:C3"), False)
  Range("Z100") = v
  Exit Sub
kapture:
  MsgBox "Vlookup fail to find " & Range("A1").Value
  On Error GoTo 0
End Sub

 

[redspanna]

Hi, can't seem to get it to work...let me explain further

in DATA sheet the VLOOKUP value is in cell H1
the range to search is also in DATA sheet through range J1:K250
the result of the VLOOKUP will be displayed in cell L1

If the result of the VLOOKUP, ie the value in L1 is #N/A then goto sheet2 and issue the error MSGBOX

hope that explains more and thanks again