VB scripting in Excel 3

[maerts]

I have a macro written that searches for a name in a workbook and then highlights the entire row to a different color. This works great when the persons name is in the workbook, however, if the name is not in the workbook I get an error, I need help adding to my macro some sort of IF or IIF statement that will allow the find and highlight to move on if the find command does not find the persons name that is specified. HELP!!!

Below is the VB that I am currently using.

Sub FindandHighlight()

For counter = 1 To 20
Cells.Find(What:="Person One", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).EntireRow.Select
Selection.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Next counter
For counter = 1 To 20
Cells.Find(What:="Person Two", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).EntireRow.Select
Selection.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Next counter
For counter = 1 To 20
Cells.Find(What:="Person Three", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).EntireRow.Select
Selection.Interior.ColorIndex = 3
ActiveCell.Offset(1, 0).Range("A1&quot.Select
Next counter
End Sub

 

[McPat]

You could add an onerror statement that either takes it to the next iteration level or displays a dialog box that tells you it didnt find anything and then exits the procedure.

 

[maerts]

I am really new to VB, if you could give me a step in the right direction as in the name of the statement I will look it up and figure out how to use it, thanks in Advance McPat

 

[HobbitK]

maerts..
In VB Help look up the Err object and the OnError Statement. It will explain it in detail.
but in your Sub do something like this...

Sub FindandHighlight()
On Error GoTo MyErrorHandler
'the rest of your code here
.
.
.
Exit Sub
MyErrorHandler:
If Err.Number = '(enter your error code here) Then
MsgBox("Search String Not Found&quot
End If

End Sub

The first Exit Sub statement is mandatory.. if Vb does not encounter that statement, the code just keeps going down the line, and your error handler will run everytime.

 

[maerts]

Thank you Hobbitk and McPat