macro-match function

  • Thread starter Thread starter yshridhar
  • Start date Start date
Y

yshridhar

Hello everybody
The following macro i copied from VB help
Sub FindFirst()
myvar = Application.WorksheetFunction.Match(9,
Worksheets(1).Range("A1:A10"), 0)
MsgBox myvar
End Sub

I want to insert a condition, if the number is not available (function
returns error) then exit. Inthis case the macro is returning error 1004.
How to modify the code?
With regards
Sreedhar
 
This would work

Sub FindFirst()
On Error GoTo Endit
myvar = Application.WorksheetFunction.Match(9,
Worksheets(1).Range("A1:A10"), 0)
MsgBox myvar
Endit:
End Sub

Rob
 
Thank you Rob. One more clarification. How to insert msgbox OnError.
With regards
Sreedhar
 
yshridhar,

Hope this is what you're after.

Sub FindFirst()
On Error GoTo Endit
myvar = Application.WorksheetFunction.Match(9,
Worksheets(1).Range("A1:A10"), 0)
MsgBox myvar
Exit Sub
Endit:
MsgBox "No match found!"
End Sub

Rob
 
You could also use:

Sub FindFirst()
dim myVar as variant
myvar = Application.Match(9, Worksheets(1).Range("A1:A10"), 0)
if iserror(myvar) then
msgbox "Error"
else
MsgBox myvar
end if
End Sub
 
Back
Top