Macro Error Help

  • Thread starter Thread starter C. Hernandez
  • Start date Start date
C

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
 
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
 
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
 
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.
 
Back
Top