Do loop erroring
 

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

Do loop erroring
 

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 . com,
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

Do loop erroring
 

Much thanks for the quick reply

Do loop erroring
 

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

"JE McGimpsey" wrote in message
...
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 . com,
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

Do loop erroring
 

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

Do loop erroring
 

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

wrote in message
ups.com...
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

Do loop erroring
 

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

wrote in message
ups.com...
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

Do loop erroring
 

Thanks Don. I'll give this a try.

Do loop erroring
 

Works like a charm. Thanks again.

Do loop erroring
 

Glad to help

--
Don Guillett
SalesAid Software

wrote in message
ups.com...
Works like a charm. Thanks again.