Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find stops macro if no match...
I have a workbook with multiple sheets that I'm trying to use a macro to find
and pull any matches and place them on a new "results" sheet. The way it's set now i have a seperate "search" sheet setup to have a specific cell used for the phrase to find and option boxes to check which sheets get searched. It runs fine till it gets to a sheet that doesn't have a match and it stops the macro and asks to end/debug the macro. Is there a way to incorporate the find method into an if statement or something else that if no matches are found it goes to the next part of code? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find stops macro if no match...
dim found as Excel.Range Set found = SomeRange.Find(conditions) if found is nothing then .... ..... the part of code executed when no matches are found ..... Else ..... ..... ..... End IF |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find stops macro if no match...
I tend to use:
Sub Whatever MyVariable = Application.Match(Item_Value,Array_or_Range,False) If IsError(MyVariable) then 'a match was not found 'do stuff Else 'a match was found 'do stuff Endif End Sub In situations where I'm looking for unique values to add to a new list, I put my code in the top part; if I'm trying to find matches to do something with the match, I use the bottom part. HTH Keith "Mr. m0le" wrote: I have a workbook with multiple sheets that I'm trying to use a macro to find and pull any matches and place them on a new "results" sheet. The way it's set now i have a seperate "search" sheet setup to have a specific cell used for the phrase to find and option boxes to check which sheets get searched. It runs fine till it gets to a sheet that doesn't have a match and it stops the macro and asks to end/debug the macro. Is there a way to incorporate the find method into an if statement or something else that if no matches are found it goes to the next part of code? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find stops macro if no match...
ok... i'm a bit of a novice at this and I guess I'm not doing something
right. I tried to get what you posted to work but i'm not filling it in correctly i guess. Below i put what code i started with till i ran into this problem. If you cound possibly incorporate what I'm posting with your post so i can understand how your code snipet is suppose to work i would greatly appriciate it. in step3 of the code... this is the first sheet i ran into that what i was searching through that wasn't on the sheet. Sub SingleSearch() 'setup1 = Sheets(Array("Magic 2010", "Alara Reborn", "Conflux", "Shards of Alara", _ "Magic Trader - Phy. NonFoil", "Magic Trader - Phy. Foil")).Select search = Range("f4").Value Range("f5").Font.ColorIndex = 3 Application.ScreenUpdating = False 'Worksheets("Your Search Results").Visible = False step1: Worksheets("Your Search Results").Visible = True Sheets("Your Search Results").Activate Range("a:iv").ClearContents Range("a:iv").ClearFormats Range("a1").Select step2: Sheets("Magic 2010").Activate Range("a1").Select ac = ActiveCell.Value CountA = 1 countb = 2 step2b: Sheets("Magic 2010").Activate ac = ActiveCell.Value Range("e1:k2").Copy Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate If ActiveCell.Value = ac Then GoTo step3 Else Sheets("Your Search Results").Activate ActiveCell.PasteSpecial Sheets("Magic 2010").Activate If CountA = 2 Then GoTo step2a Else Range(ActiveCell, ActiveCell.Offset(0, 6)).Copy CountA = CountA + 1 step2a: Range(ActiveCell, ActiveCell.Offset(0, 6)).Copy Sheets("Your Search Results").Activate ActiveCell.Offset(countb, 0).Select ActiveCell.PasteSpecial countb = countb + 1 GoTo step2b step3: ActiveCell.Offset(1, 0).Select Sheets("Alara Reborn").Activate Range("a1").Select ac = ActiveCell.Value CountA = 1 countb = 2 step3b: Sheets("Alara Reborn").Activate ac = ActiveCell.Value Range("e1:k2").Copy If Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Select = "" Then GoTo step4 Else If ActiveCell.Value = ac Then GoTo step4 Else Sheets("Your Search Results").Activate ActiveCell.PasteSpecial Sheets("Magic 2010").Activate If CountA = 2 Then GoTo step2a Else Range(ActiveCell, ActiveCell.Offset(0, 6)).Copy CountA = CountA + 1 step3a: Range(ActiveCell, ActiveCell.Offset(0, 6)).Copy Sheets("Your Search Results").Activate ActiveCell.Offset(countb, 0).Select ActiveCell.PasteSpecial countb = countb + 1 GoTo step3b step4: Application.ScreenUpdating = False End Sub "ker_01" wrote: I tend to use: Sub Whatever MyVariable = Application.Match(Item_Value,Array_or_Range,False) If IsError(MyVariable) then 'a match was not found 'do stuff Else 'a match was found 'do stuff Endif End Sub In situations where I'm looking for unique values to add to a new list, I put my code in the top part; if I'm trying to find matches to do something with the match, I use the bottom part. HTH Keith "Mr. m0le" wrote: I have a workbook with multiple sheets that I'm trying to use a macro to find and pull any matches and place them on a new "results" sheet. The way it's set now i have a seperate "search" sheet setup to have a specific cell used for the phrase to find and option boxes to check which sheets get searched. It runs fine till it gets to a sheet that doesn't have a match and it stops the macro and asks to end/debug the macro. Is there a way to incorporate the find method into an if statement or something else that if no matches are found it goes to the next part of code? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't find bug because program justs Stops !! | Excel Programming | |||
Macro stops when one a file | Excel Programming | |||
excel macro stops | Excel Programming | |||
Macro to compare, find match and copy between workbooks | Excel Programming | |||
My Macro stops | Excel Programming |