Find Multiple Occurence Of Text In A Column

[Steve]

Orders come in by Outlook Express email. I copy the email to an Excel
worksheet and then I process the order. Orders may be multiple items. Each
item in the order begins with the words "Item Ordered" in column A which is
then followed by three rows regarding the item ordered and then a blank row.
How do I write the following in Excel VBA:

Find First "Item Ordered" in column A
<< Code to process the item ordered>>
Find Next "Item Ordered" in column A
If "Item Ordered" found Then
<< Code to process the item ordered>>
Else
Message "All items in order have been processed"
End If

Thanks for all help!

Steve

 

[JW]

This will cycle through all of the cells in column A. You can tweak
this to look for whatever and wherever you need.
Dim r as Range, mRange
Set mRange=Range("A2:A" & Range("A65536").End(xlUp.Row)
For Each r in mRange
If Left(r.Value,12)="Item Ordered" Then
'Place code to process order here
End If
Next r
Set mRange=Nothing

 

[JW]

Oops. Had a type.
Replace:
Set mRange=Range("A2:A" & Range("A65536").End(xlUp.Row)

With:
Set mRange=Range("A2:A" & Range("A65536").End(xlUp).Row

 

[Jim Thomlinson]

Give something like this a try...

Sub FindAndProcess()
Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="Item Ordered", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
MsgBox "Found " & rngFound.Address 'do stuff here
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
MsgBox "That is all of em..."
Else
MsgBox "Didn't find a one of em"
End If
End Sub

 

[Jim Thomlinson]

Are you sure about that line. The brackets don't line up (among other
issues). This might be better...

Set mRange = Range("A2", Cells(Rows.Count, "A").End(xlUp))

Also note that you have declared mRange as a variant instead of a range
which while not strictly incorrect is a bad practice in general...

 

[Steve]

Thanks, JW. I appreciate you taking the time to help!

Steve

 

[Steve]

Jim, Thanks for taking the time to help!

Steve

Give something like this a try...

Sub FindAndProcess()
Dim rngFound As Range
Dim rngToSearch As Range
Dim strFirstAddress As String

Set rngToSearch = Sheets("Sheet1").Columns("A")
Set rngFound = rngToSearch.Find(What:="Item Ordered", _
LookAt:=xlWhole, _
LookIn:=xlValues, _
MatchCase:=False)
If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
MsgBox "Found " & rngFound.Address 'do stuff here
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
MsgBox "That is all of em..."
Else
MsgBox "Didn't find a one of em"
End If
End Sub

 

[JW]

Jim, not sure why, but I did leave out a parenthesis on my last post.
Should have been:
Set mRange=Range("A2:A" & Range("A65536").End(xlUp).Row)
That is just hte method that I use because I have ran into problems
using the Rows.Count method. As for the mRange variable issue, that
was a type. It certainly should have been declared as a Range. Just
an oversight on my part.