"List splitting..." a challenge!

some hints

Dave,

First, find the hyphen. Store this as a variable (like strBase) remember to include the hyphen as part of the strBase variable. Then see if there is a comma. If there isn't one, then move down to the next cell. If there is one then you want the data to the right of the space. Which you want to concatenate (&) with the strBase value. Then insert a row and put in the new value.

Since you're inserting rows, you may want to do this from the bottom up (in a For ... Next loop you'd have to use Step -1).

Using a combination of worksheet functions (in adjacent cells to get the syntax for use in the macro) and macro recording you should be able to finish this up in no time.

See if these hints help at all.

enjoy

z

Re: some hints

Cheers for the tips...
Now i see what i'm supposed to be doing but, i dont really know how to do it (i'm very new to vba and dont know how to construct the loops, let alone how to go about setting up the variables to be varied lengths upto and including the hyphen!). Any more advice or clues as to what i need to do next?

Sorry to be a pain. ,

: Hi all! : Due to the incredible sucess of my last post (problem solved inside of 4 hours!) thanks to "Dank" and "Tom D", I would like to call on your collective help once more. : I have a list which looks something like this

some more hints

Dave,

Open up Excel's help and read about the worksheet functions like Search and Mid and Left (one's that are used to manipulate text; you can also find them by hitting the f(x) button on the toolbar). Then, at first, try to "deconstruct" your text manually by creating a formula, or a series of formulas, in a worksheet cell. This way you can then learn what you'll need to do to have VBA do this programmatically.

Speaking of VBA, open it up (alt-F11) and read the help on "For ... Next" loops. They'll have little code snippets that you can read through (some of them you can copy into a module and run) so that you can see how they work. While in the VBA help do a search for "loops" and you'll get a listing of all the different kinds available.

Not trying to be difficult here, but you seem like you want to learn how to do this stuff and one of the best resources is right on your own machine.

Also, be sure to read other folks' posts on here. You can learn alot by seeing how other problems are solved (I know I do!).

have fun

ps: you could do alot worse than picking up a book on Excel VBA. John Walkenbach's are especially good.

Cheers for the tips... Now i see what i'm supposed to be doing but, i dont really know how to do it (i'm very new to vba and dont know how to construct the loops, let alone how to go about setting up the variables to be varied lengths upto and including the hyphen!). Any more advice or clues as to what i need to do next? Sorry to be a pain. : Dave, : First, find the hyphen. Store this as a variable (like strBase) remember to include the hyphen as part of the strBase variable. Then see if there is a comma. If there isn't one, then move down to the next cell. If there is one then you want the data to the right of the space. Which you want to concatenate (&) with the strBase value. Then insert a row and put in the new value. : Since you're inserting rows, you may want to do this from the bottom up (in a For ... Next loop you'd have to use Step -1). : Using a combination of worksheet functions (in adjacent cells to get the syntax for use in the macro) and macro recording you should be able to finish this up in no time. : See if these hints help at all. : : enjoy :

Re: some more hints

Tried the help option but, discovered that our "inept" IT dept failed to install it! Nothing is ever easy... still, its there now and seems to be useful indeed!
Thanks for your continued help,
Dave

ps. which book of John Walkenbach's would you think would make for the best starting point? , Open up Excel's help and read about the worksheet functions like Search and Mid and Left (one's that are used to manipulate text; you can also find them by hitting the f(x) button on the toolbar). Then, at first, try to "deconstruct" your text manually by creating a formula, or a series of formulas, in a worksheet cell. This way you can then learn what you'll need to do to have VBA do this programmatically. Speaking of VBA, open it up (alt-F11) and read the help on "For ... Next" loops. They'll have little code snippets that you can read through (some of them you can copy into a module and run) so that you can see how they work. While in the VBA help do a search for "loops" and you'll get a listing of all the different kinds available. Not trying to be difficult here, but you seem like you want to learn how to do this stuff and one of the best resources is right on your own machine. Also, be sure to read other folks' posts on here. You can learn alot by seeing how other problems are solved (I know I do!). have fun

ps: you could do alot worse than picking up a book on Excel VBA. John Walkenbach's are especially good. : Now i see what i'm supposed to be doing but, i dont really know how to do it (i'm very new to vba and dont know how to construct the loops, let alone how to go about setting up the variables to be varied lengths upto and including the hyphen!). Any more advice or clues as to what i need to do next? : Sorry to be a pain.

Re: some more hints

Dave,

Depending on the version of Excel you are using, John's book "Excel 2000 Power Programming with VBA" should do the trick. He has one for Excel 95. Excel 97 is close enough to 2000 that you can use the above book easily enough.

enjoy

(bet your request to install Excel help took IT by surprise! LOL "Nobody ever reads Help!" LOL)

Tried the help option but, discovered that our "inept" IT dept failed to install it! Nothing is ever easy... still, its there now and seems to be useful indeed! Thanks for your continued help, Dave ps. which book of John Walkenbach's would you think would make for the best starting point?

Re: some more hints

It took longer to get it to install the help files than it did to convince the library to buy me four books (including the one you mentioned)... doesn't seem quite right somehow but, it worked!

Thanks once again for your help,
Dave , Depending on the version of Excel you are using, John's book "Excel 2000 Power Programming with VBA" should do the trick. He has one for Excel 95. Excel 97 is close enough to 2000 that you can use the above book easily enough. enjoy (bet your request to install Excel help took IT by surprise! LOL "Nobody ever reads Help!" LOL) : Thanks for your continued help, : Dave : ps. which book of John Walkenbach's would you think would make for the best starting point?

Re: some more hints

Dave,

Now back to your "challenge."

The initial path that I was leading you toward was the creation of what John Walkenbach calls a "megaformula." He covers the creation of such a beast on pages 52 - 54 in his book. That's probably the first topic you'll want to read on.

After you get a little reading under your belt and you've given my hints a shot, you might want to re-post any questions you have at the top of the board. This thread is moving down the page a ways.

have fun

It took longer to get it to install the help files than it did to convince the library to buy me four books (including the one you mentioned)... doesn't seem quite right somehow but, it worked! Thanks once again for your help, Dave : Dave, : Depending on the version of Excel you are using, John's book "Excel 2000 Power Programming with VBA" should do the trick. He has one for Excel 95. Excel 97 is close enough to 2000 that you can use the above book easily enough. : enjoy : : (bet your request to install Excel help took IT by surprise! LOL "Nobody ever reads Help!" LOL)