Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Macro error | Excel Discussion (Misc queries) | |||
Macro error : Application-defined or object-defined error | Excel Discussion (Misc queries) | |||
Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) |