Macro Error Help

[C. Hernandez]

Hello, I am fairly new at VBA and created a macro to search for particular
text and then highlight the entire row when the text is found, but if one of
the text values is missing, the macro errors. I believe the Dim V is the way
to go, but I've it written wrong. Please help.

This is my formatting code that if all the values are found it works fine.
Cells.Find(What:="COMPENSATION & FRINGE", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
With Selection.EntireRow.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Cells.Find(What:="ADVERTISING,DIRECT MAIL,COLL2", After:=ActiveCell,
LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
With Selection.EntireRow.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

 

[Sheeloo]

Since FIND returns an error when it fails to find the search string you need
to use
ON ERROR before the FIND..

ON ERROR RESUME NEXT
or
ON ERROR GOTO 0
or
ON ERROR GOTO ErrorHandler

and have the label at the end of the macro
ErorHandler:
'Code to handle error

 

[Don Guillett]

Just change a,b,c,d to your text. Since you are looking for PART, could even
be "comp","adv", UNLESS they are part of any possible word. So, better to
use the whole text.


Sub Findemall()
On Error Resume Next
For Each mStr In Array("a", "b", "c", "d")
Cells.Find(What:=mStr, After:=ActiveCell, _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).entirerow.Interior.ColorIndex = 36
Next mStr
End Sub

 

[Sheeloo]

ON ERROR GOTO 0 was just given an illustration.

"The first form, On Error Goto 0, is the default mode in VBA. This
indicates that when a run time error occurs VBA should display its standard
run time error message box, allowing you to enter the code in debug mode or
to terminate the VBA program. When On Error Goto 0 is in effect, it is the
same as having no enabled error handler. Any error will cause VBA to display
its standard error message box."

See http://www.cpearson.com/excel/ErrorHandling.htm for details.

 

[C. Hernandez]

Thank you so much! That worked like a charm.

 

[Don Guillett]

Glad to help