Macro Error Help
 

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
--
~ Chris

Macro Error Help
 

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

"C. Hernandez" wrote:

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
--
~ Chris

Macro Error Help
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C. Hernandez" wrote in message
...
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
--
~ Chris

Macro Error Help
 

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" wrote:

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
--
~ Chris

Macro Error Help
 

Thank you so much! That worked like a charm.
--
~ Chris


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C. Hernandez" wrote in message
...
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
--
~ Chris

Macro Error Help
 

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C. Hernandez" wrote in message
...
Thank you so much! That worked like a charm.
--
~ Chris


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C. Hernandez" wrote in message
...
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
--
~ Chris