I am a novice at writing macros and need what I hope is some easy to
obtain advice. I am trying to create a maco that will search for some
text and when it finds it will then delete the row where the text
appears and then continue searching for the same text. I have a simple
do loop that works, but I don't know how to make it stop when it
doesn't find the text. It errors when it can't find the text.

Here is the code:

Do
Cells.Find(What:="Jackson Fish", LookAt:=xlWhole).Activate
Range(Selection, Selection.EntireRow).Select
Selection.Delete Shift:=xlUp
Loop

Thanks in advance

One way:

Dim rFound As Range
Set rFound = Cells.Find( _
What:="Jackson Fish", _
LookAt:=xlWhole)
Do Until rFound Is Nothing
rFound.EntireRow.Delete
Set rFound = Cells.FindNext
Loop




In article <(E-Mail Removed)>,
(E-Mail Removed) wrote:

> I am a novice at writing macros and need what I hope is some easy to
> obtain advice. I am trying to create a maco that will search for some
> text and when it finds it will then delete the row where the text
> appears and then continue searching for the same text. I have a simple
> do loop that works, but I don't know how to make it stop when it
> doesn't find the text. It errors when it can't find the text.
>
> Here is the code:
>
> Do
> Cells.Find(What:="Jackson Fish", LookAt:=xlWhole).Activate
> Range(Selection, Selection.EntireRow).Select
> Selection.Delete Shift:=xlUp
> Loop
>
> Thanks in advance

Much thanks for the quick reply

Also, IF? all finds are in the same column, use
columns("b").find
instead of cells.
--
Don Guillett
SalesAid Software
(E-Mail Removed)
"JE McGimpsey" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> One way:
>
> Dim rFound As Range
> Set rFound = Cells.Find( _
> What:="Jackson Fish", _
> LookAt:=xlWhole)
> Do Until rFound Is Nothing
> rFound.EntireRow.Delete
> Set rFound = Cells.FindNext
> Loop
>
>
>
>
> In article <(E-Mail Removed)>,
> (E-Mail Removed) wrote:
>
>> I am a novice at writing macros and need what I hope is some easy to
>> obtain advice. I am trying to create a maco that will search for some
>> text and when it finds it will then delete the row where the text
>> appears and then continue searching for the same text. I have a simple
>> do loop that works, but I don't know how to make it stop when it
>> doesn't find the text. It errors when it can't find the text.
>>
>> Here is the code:
>>
>> Do
>> Cells.Find(What:="Jackson Fish", LookAt:=xlWhole).Activate
>> Range(Selection, Selection.EntireRow).Select
>> Selection.Delete Shift:=xlUp
>> Loop
>>
>> Thanks in advance

This is the code I am using now, but it is only finding one instance
of "Year-to-Date" where I want it to find all instances. Not sure what
I am missing.

Dim rFound As Range
Dim szFirst As String
Dim iCount As Integer

Set rFound = Cells.Find(What:="Year-to-Date ", LookAt:=xlPart)
iCount = 0
Do Until rFound Is Nothing
If szFirst = "" Then
szFirst = rFound.Address
ElseIf rFound.Address = szFirst Then
Exit Do
End If

rFound.EntireRow.Select
ActiveCell.Offset(0, 4).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Summary!C[4]:C[6],
3,FALSE)"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 =
"=RC[-3]*10000000*VLOOKUP(RC[-5],Summary!C[3]:C[5],2,FALSE)"
iCount = iCount + 1
Set rFound = Cells.FindNext
Loop

Jack

Try it this way. Notice I am looking in only the appropriate range and NOT
selecting anything. You could have found an excellent example in the help
index looking for FINDNEXT. This is only looking for instances where your
value is part of a string such as
Year-to-Date ss yes
Year-to-Date no
xx Year-to-Date ss yes

Sub findemall_Don()
With Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).row)
Set c = .Find("Year-to-Date ", Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 4).FormulaR1C1 = _
"=VLOOKUP(RC[-4],Summary!C[4]:C[6],3,0)"
c.Offset(, 1).FormulaR1C1 = _
"=RC[-3]*10000000*VLOOKUP(RC[-5],Summary!C[3]:C[5],2,0)"

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub


--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This is the code I am using now, but it is only finding one instance
> of "Year-to-Date" where I want it to find all instances. Not sure what
> I am missing.
>
> Dim rFound As Range
> Dim szFirst As String
> Dim iCount As Integer
>
> Set rFound = Cells.Find(What:="Year-to-Date ", LookAt:=xlPart)
> iCount = 0
> Do Until rFound Is Nothing
> If szFirst = "" Then
> szFirst = rFound.Address
> ElseIf rFound.Address = szFirst Then
> Exit Do
> End If
>
> rFound.EntireRow.Select
> ActiveCell.Offset(0, 4).Select
> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Summary!C[4]:C[6],
> 3,FALSE)"
> ActiveCell.Offset(0, 1).Select
> ActiveCell.FormulaR1C1 =
> "=RC[-3]*10000000*VLOOKUP(RC[-5],Summary!C[3]:C[5],2,FALSE)"
> iCount = iCount + 1
> Set rFound = Cells.FindNext
> Loop
>
> Jack
>

Sub findemall_Don() 'yes
With Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).row)
Set c = .Find("Year-to-Date ", Lookat:=xlPart)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, 4).FormulaR1C1 = _
"=VLOOKUP(RC[-4],Summary!C[4]:C[6],3,0)"
'changed this line
c.Offset(, 5).FormulaR1C1 = _
"=RC[-3]*10000000*VLOOKUP(RC[-5],Summary!C[3]:C[5],2,0)"

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> This is the code I am using now, but it is only finding one instance
> of "Year-to-Date" where I want it to find all instances. Not sure what
> I am missing.
>
> Dim rFound As Range
> Dim szFirst As String
> Dim iCount As Integer
>
> Set rFound = Cells.Find(What:="Year-to-Date ", LookAt:=xlPart)
> iCount = 0
> Do Until rFound Is Nothing
> If szFirst = "" Then
> szFirst = rFound.Address
> ElseIf rFound.Address = szFirst Then
> Exit Do
> End If
>
> rFound.EntireRow.Select
> ActiveCell.Offset(0, 4).Select
> ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Summary!C[4]:C[6],
> 3,FALSE)"
> ActiveCell.Offset(0, 1).Select
> ActiveCell.FormulaR1C1 =
> "=RC[-3]*10000000*VLOOKUP(RC[-5],Summary!C[3]:C[5],2,FALSE)"
> iCount = iCount + 1
> Set rFound = Cells.FindNext
> Loop
>
> Jack
>

Thanks Don. I'll give this a try.

Works like a charm. Thanks again.

Glad to help

--
Don Guillett
SalesAid Software
(E-Mail Removed)
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Works like a charm. Thanks again.
>