Can't get out of my loop
 

I have a situation where I run a macro that looks for
certain words within the active spreadsheet. If no match
is found I return a "No Match" in a adjacent cell to my
list.

After this macro runs I want to find where No Matches are
present and add that name (Negitive 6 cells away from "No
Match") to my master list of customer in Alfbetical
order. I also want to include the next cell in order when
coping.

Once I have the correct info copied I want to scroll over
to master list and add the new customer. In doing so I
have to insert a row and paste the new customer info that
I just copied to my clipboard.

The problem I am running into is, once my macro below
does not find a "No Match" it wants to crash. What I want
to do is exit the loop so I can sort the list in
alphabetical order and continue doing other things.

Ideally when I have to enter a new customer into my
master list I would want it to be added to the top of the
my list so I can see my new customers for that piticular
run.

Sub Macro11()

Start:
Range("AF2").Select
Application.DisplayAlerts = False
Cells.Find(What:="No Match", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If ActiveCell.Text = "No Match" Then
ActiveCell.ClearContents
Do
ActiveCell.Offset(0, -6).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(0, -24).Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
GoTo Start
Loop
End If
End Sub

Thanks for any help.
Pete W.

Can't get out of my loop
 

pete,
this is a common problem. if it can not find a match excel returns an error
value
do
dim celltofind as range
set celltofind = Cells.Find(What:="No Match", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If not celltofind is nothing then 'cell found enter code here
celltofind.activate
else 'cell not found enter here
msgbox "No Match not found"
end if



"Pete" wrote:

I have a situation where I run a macro that looks for
certain words within the active spreadsheet. If no match
is found I return a "No Match" in a adjacent cell to my
list.

After this macro runs I want to find where No Matches are
present and add that name (Negitive 6 cells away from "No
Match") to my master list of customer in Alfbetical
order. I also want to include the next cell in order when
coping.

Once I have the correct info copied I want to scroll over
to master list and add the new customer. In doing so I
have to insert a row and paste the new customer info that
I just copied to my clipboard.

The problem I am running into is, once my macro below
does not find a "No Match" it wants to crash. What I want
to do is exit the loop so I can sort the list in
alphabetical order and continue doing other things.

Ideally when I have to enter a new customer into my
master list I would want it to be added to the top of the
my list so I can see my new customers for that piticular
run.

Sub Macro11()

Start:
Range("AF2").Select
Application.DisplayAlerts = False
Cells.Find(What:="No Match", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Activate
If ActiveCell.Text = "No Match" Then
ActiveCell.ClearContents
Do
ActiveCell.Offset(0, -6).Range("A1:B1").Select
Selection.Copy
ActiveCell.Offset(0, -24).Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
GoTo Start
Loop
End If
End Sub

Thanks for any help.
Pete W.