Match Error Handling

[CB]

I am referencing a project name in one workbook and using the match function
to find the same in another workbook and returning a number, the row which
the project name was found. What I need to have happen is if there is no
match an error message will pop up telling the user there is no match. My
code keeps quitting once there is no match how can I make it continue to the
error message.

My code is below:


Sub AutoPopulate()

Dim PRow As Int
Call OpenWorkbook

'Tells us what Row the project is in
PRow =
Application.WorksheetFunction.Match(Workbooks("TestOF.xls").Worksheets(ActiveTab).Range("d2"),
Workbooks("milestones.xls").Worksheets("data sheet").Range("A6:A400"), 0)

If IsError(PName) Then
MsgBox ("Project X can not be found please make sure the name appears
exactly as it does in PTT-PMA")
Else

Thanks

 

[RyanH]

This worked for me. If the MATCH function does not find a match it throws an
error number = 1004. I setup the Error Handler to display your message if
error 1004 occurs.

Option Explicit

Sub AutoPopulate()

Dim PRow As Integer

Call OpenWorkbook

'Tells us what Row the project is in
On Error GoTo ErrorHandler
PRow =
WorksheetFunction.Match(Workbooks("TestOF.xls").Worksheets(ActiveTab).Range("D2"), _
Workbooks("milestones.xls").Worksheets("data
sheet").Range("A6:A400"), 0)

ErrorHandler:
If Err.Number = 1004 Then
MsgBox "Project X can not be found please make sure the name appears
exactly as it does in PTT-PMA."
End If

End Sub

Hope this helps! If so, please let me know and click "YES" below.

 

[Dave Peterson]

You could also drop the .worksheetfunction and use most of your code:

Sub AutoPopulate()

Dim PRow As Variant 'could be an error
Call OpenWorkbook

'Tells us what Row the project is in
PRow = Application.Match(Workbooks("TestOF.xls") _
.Worksheets(ActiveTab).Range("d2").value, _
Workbooks("milestones.xls").Worksheets("data sheet") _
.Range("A6:A400"), 0)

'and check pRow--not sure what PName is
If IsError(PRow) Then
MsgBox "Project X can not be found please make sure the name appears " _
& "exactly as it does in PTT-PMA"
Else
....