VBA: if error show msgbox else proceed

[flyerr]

I have the following code and want the MsgBox ("Error. Please input a valid Starting Position.") to show if there is error in x=step, and want the MsgBox ("task completed.") to show if no error in x= step.

How can I code it? Thank you!!!

Sub Historical()
On Error GoTo Errormask
x = Application.WorksheetFunction _
.Match(Cells(107, 2).Value, Range("A1:A374"), 0)
'error happens on this step.'
Errormask:
MsgBox ("Error. Please input a valid Starting Position.")

"this part is the VBA procedure i need if not error''

MsgBox ("task completed.")
End Sub

 

[Jonmo1]

Welcome to the board.

As you see, using worksheetfunction in VBA, it stops on all errors, regardless of On Error goto statements...

Drop the worksheetfunction, and just use Application.
And assign the result of the function to a variable (as you did)
Then test the variable for error..

Try

Sub Historical()
Dim x As Long
x = Application.Match(Cells(107, 2).Value, Range("A1:A374"), 0)
If IsError(x) Then
    MsgBox ("Error. Please input a valid Starting Position.")
    Exit Sub
End If
'this part is the VBA procedure i need if not error
MsgBox ("task completed.")
End Sub

 

[flyerr]

Thank you for the quick reply!! Excited to join the board. The code works when there is no error. but the MsgBox ("Error. Please input a valid Starting Position.") won't show when there is error. The defaut error box from Excel showed.. Why is this happening?
THank you.

Sub Historical()
Dim x As Long
x = Application.Match(Cells(107, 2).Value, Range("A1:A374"), 0)
If IsError(x) Then
MsgBox ("Error. Please input a valid Starting Position.")
Exit Sub
End If
'this part is the VBA procedure i need if not error
MsgBox ("task completed.")
End Sub

 

[Jonmo1]

Sorry,

Dim x As Variant

 

[flyerr]

It works!! This is great!
Thanks a million! jonmo1

 

[Scott Huish]

You could also do that with VBA functions instead of worksheet functions:

Sub test()
Dim c As Range
Set c = Range("A1:A374").Find(Range("DC2"), lookat:=xlWhole)
If c Is Nothing Then
    MsgBox "Please input a valid starting position"
    Exit Sub
End If
' Rest of Code Here
End Sub

 

[GTO]

Hi JonMo,


I tried this in Excel2000:

Option Explicit
        
Sub Historical()
Dim x As Long
    
    On Error GoTo Errormask
    x = Application.WorksheetFunction.Match(Cells(107, 2).Value, Range("A1:A374"), 0)
    
    x = Application.Match(Cells(107, 2).Value, Range("A1:A374"), 0)
    
    MsgBox "Found a match at row: " & x, 0, vbNullString
    
Exit Sub
Errormask:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description
    Debug.Print "Error: (" & Err.Number & ") " & Err.Description
End Sub

If the value is not in the lookup_array, ...WorksheetFunction.Match jumped to the error handler, returning "Error: (1004) Unable to get the Match property of the WorksheetFunction class".

REM'ing the first one, ...Match jumped to the error handler, returning "Error: (13) Type mismatch".

I am probably missing something, but the the on error did not seem to get ignored?

Thank you so much,

Mark

 

[GTO]

In followup to #7...

Hi again JonMo,

My bad, although it worked for me, I see that results are spotty: http://www.excelforum.com/excel-programming/358571-application-match.html

 

[Jonmo1]

See post #4 above