macro-match function

[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

 

[RobN]

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

 

[yshridhar]

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

 

[RobN]

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

 

[yshridhar]

Thank you Rob. This is what i want.
With regards
Sreedhar

 

[Dave Peterson]

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