Find stops macro if no match...

  • Thread starter Thread starter Mr. m0le
  • Start date Start date
M

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