Do loop erroring

[crumismydaddy]

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

 

[JE McGimpsey]

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

 

[crumismydaddy]

Much thanks for the quick reply

 

[Don Guillett]

Also, IF? all finds are in the same column, use
columns("b").find
instead of cells.

 

[crumismydaddy]

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

 

[Don Guillett]

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]

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

 

[crumismydaddy]

Thanks Don. I'll give this a try.

 

[crumismydaddy]

Works like a charm. Thanks again.

 

[Don Guillett]

Glad to help