| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Multiple Occurence Of Text In A Column
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
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 Steve wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
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 JW wrote: 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 Steve wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
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... -- HTH... Jim Thomlinson "JW" wrote: 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 JW wrote: 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 Steve wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
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. Jim Thomlinson wrote: 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... -- HTH... Jim Thomlinson "JW" wrote: 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 JW wrote: 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 Steve wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
Thanks, JW. I appreciate you taking the time to help!
Steve "JW" wrote in message ups.com... 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 JW wrote: 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 Steve wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
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 -- HTH... Jim Thomlinson "Steve" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Find Multiple Occurence Of Text In A Column
Jim, Thanks for taking the time to help!
Steve "Jim Thomlinson" wrote in message ... 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 -- HTH... Jim Thomlinson "Steve" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find a First Occurence in a Column | Excel Discussion (Misc queries) | |||
| Find Last Occurence | New Users to Excel | |||
| find 2nd occurence | Excel Discussion (Misc queries) | |||
| Find Last Occurence in a Range with VBA | Excel Discussion (Misc queries) | |||
| Find first occurence of specific data in a column. | Excel Programming | |||
![Steve[_91_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
