pulling from 'line' inside cell- best way to reference in VBA?
 

I have a worksheet where the user uses one row for an item; each item can
have zero to about 6 subitems that these users put each subitem (say, in
column G) on separate lines for readability using <cntl-enter.

They've asked me to use VBA code to detect a certain type of entry (with a
userform, so they can select what type of entry to search for). When that
entry is found, they want me to pull _just_ the corresponding line from
Column H.

Basically;
------------------------------------
Item SubitemA DetailA
SubitemB DetailB
SubitemC DetailC
-------------------------------------
Item SubitemA DetailA
SubitemB DetailB
SubitemC DetailC
-------------------------------------

So if the select Subitem B, I should pull each Detail B and pull it over to
the report I'm creating.

Right now, I'm cycling through each cell to see if DetailB is present, then
trying to parse based on how many returns are in the cell... is there a
better/easier way? Has anyone done this before that could provide a code
snippet for me to test and adapt?

Many Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

pulling from 'line' inside cell- best way to reference in VBA?
 

It might make it easier to parse if you used split() (added in xl2k) to separate
the entries based on vblf's.

Then you could loop through the array looking for what you want.

instr() may help you look through the original string first.

KR wrote:

I have a worksheet where the user uses one row for an item; each item can
have zero to about 6 subitems that these users put each subitem (say, in
column G) on separate lines for readability using <cntl-enter.

They've asked me to use VBA code to detect a certain type of entry (with a
userform, so they can select what type of entry to search for). When that
entry is found, they want me to pull _just_ the corresponding line from
Column H.

Basically;
------------------------------------
Item SubitemA DetailA
SubitemB DetailB
SubitemC DetailC
-------------------------------------
Item SubitemA DetailA
SubitemB DetailB
SubitemC DetailC
-------------------------------------

So if the select Subitem B, I should pull each Detail B and pull it over to
the report I'm creating.

Right now, I'm cycling through each cell to see if DetailB is present, then
trying to parse based on how many returns are in the cell... is there a
better/easier way? Has anyone done this before that could provide a code
snippet for me to test and adapt?

Many Thanks,
Keith

--
The enclosed questions or comments are entirely mine and don't represent the
thoughts, views, or policy of my employer. Any errors or omissions are my
own.

--

Dave Peterson