Find stops macro if no match...

[Mr. m0le]

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?

 

[Przemek]

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

 

[ker_01]

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]

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