Macro to copy text between delimeters

[Vic]

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

 

[matt]

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

 

[Dave Peterson]

You have a response at your other post, too.

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]

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.

 

[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.- Hide quoted text -

- Show quoted text -

You can copy by using the .Copy method.

Here's a quick example:

Range("a" & comprow & ":a" & zipRow - 1).Copy
'or whatever the data set is that you are copying

Note: There are MANY ways to copy ranges.

Matt