Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text between delimeters
I am trying to write a macro in Excel to select text between cells
which contain words. Two challenges: 1.) I need to search for a cell that contains only one-single word "item:" and then copy the contents of the cell just-above that cell 2.) I need to search for a cell that contains only one-single word "component(s):" and then copy all of the rows below that until I find a cell that contains only the word "Zip" Any samples would help for either of these challenges ~Vic |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text between delimeters
On Apr 6, 7:14 am, "Vic" wrote:
I am trying to write a macro in Excel to select text between cells which contain words. Two challenges: 1.) I need to search for a cell that contains only one-single word "item:" and then copy the contents of the cell just-above that cell 2.) I need to search for a cell that contains only one-single word "component(s):" and then copy all of the rows below that until I find a cell that contains only the word "Zip" Any samples would help for either of these challenges ~Vic Vic, You can use the Find function, the Offset Property, and the Row or Address property to find the data that you are looking for. (The code has not been tested). In order to offset from "item:" you can right something like itemOffset = Columns("a").Find(What:="item:").Offset(-1,0).Value You can use the same idea to find the row locations of "component(s):" and "Zip" compRow = Columns("a").Find(What:="component(s):").Row zipRow = Columns("a").Find(What:="Zip").Row You can then copy the data inbetween the Rows/Addresses. The macro recorder will give you a Cells.Find when you record the Find option; however, don't be afraid to change "Cells" to a more specific location. Also, the Find function has a FindNext parameter (and there is a FindNext method) that will assist you if you have multiple words to find. Matt |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text between delimeters
You have a response at your other post, too.
Vic wrote: I am trying to write a macro in Excel to select text between cells which contain words. Two challenges: 1.) I need to search for a cell that contains only one-single word "item:" and then copy the contents of the cell just-above that cell 2.) I need to search for a cell that contains only one-single word "component(s):" and then copy all of the rows below that until I find a cell that contains only the word "Zip" Any samples would help for either of these challenges ~Vic -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to copy text between delimeters
On Apr 6, 7:55 am, "matt" wrote:
On Apr 6, 7:14 am, "Vic" wrote: I am trying to write a macro in Excel to select text between cells which contain words. Two challenges: 1.) I need to search for a cell that contains only one-single word "item:" and then copy the contents of the cell just-above that cell 2.) I need to search for a cell that contains only one-single word "component(s):" and then copy all of the rows below that until I find a cell that contains only the word "Zip" Any samples would help for either of these challenges ~Vic Vic, You can use the Find function, the Offset Property, and the Row or Address property to find the data that you are looking for. (The code has not been tested). In order to offset from "item:" you can right something like itemOffset = Columns("a").Find(What:="item:").Offset(-1,0).Value You can use the same idea to find the row locations of "component(s):" and "Zip" compRow = Columns("a").Find(What:="component(s):").Row zipRow = Columns("a").Find(What:="Zip").Row You can then copy the data inbetween the Rows/Addresses. The macro recorder will give you a Cells.Find when you record the Find option; however, don't be afraid to change "Cells" to a more specific location. Also, the Find function has a FindNext parameter (and there is a FindNext method) that will assist you if you have multiple words to find. Matt Thank you. I did the simpler of the two scenarios and it worked great. I cannot understand once i have both compRow and zipRow how to select and copy the text in-between the two. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy text between two different delimeters | New Users to Excel | |||
Find a string between two delimeters and replace with text | Excel Programming | |||
copy text from 1 workbook to another by macro | Excel Worksheet Functions | |||
Macro to copy only certain text | Excel Programming | |||
Import text file into excel with preset file layout, delimeters VBA | Excel Programming |