String concatenation of values

Hi,

Im a newbie in the world of Macro Excel 2003 and I need help in creating VBA code that would concat value in a cell and creates multiple records from those. I have attached sample file , Sample tab is the input data/worksheet and Output tab is the worksheet that to be created.

PS:
Subgroup A is compatible to all Members (1 to 8)
Subgroup B is compatible to Member 7 only.
Subgroup C is compatible to Member 8 only.
If 3 subgroups exist in cell (A , B & C), Members 1 to 6 is for Subgroup A and 7 is for Subgroup B, 8 is for subgroup C.
If only subgroup A exist, all Members will used Subgroup A.

Try this, you can remove the colors from the data sheet, it will add them for any groups it expands. Single row entries will remain uncolored, same as your example sheet.

Please Login or Register  to view this content.

I have also another problem. This is also related to string concatenation. Do someone know how to modify the code below to transfer data in which its cells format is text?

PS. All Members should start with "00". Please see attachment for the sample file.

Please amend the post above, put code tags around the code as per forum rules, or remove the code as it's not needed to be posted twice.

Looking at your changes to the code, you've really gone a long way from the original parameters.

If your original set of criteria bear no relation to your real-life needs here, you'll need to properly stipulate how the groups and members relate in the new data. In your original post you stated it clearly and the macro was designed to match that criteria.

Also, why are you mixing text strings and numbers? What could be more confusing? If you need a 4 digit "code" then stick an alpha character in there so that it really is text. Using 0007 and 0003 is just asking for problems to crop now and again and again and again... I see this all the time and am consistently flummoxed by it.

Anyway, your new question "seems" to be an extension of your original, but it's not actually. We need to start over with another methodology.

Perhaps you could simply design a sheet with a two-column lookup table. Column A has the Members and column B all the subgroups related to that Member. At least that way we have a single point of reference for anything you add later.

Well, these text-mixed-numerics really bug me, sorry for going off so strongly.

Here's an amendment that will at least put the strings into column C in the 0000 format.

Please Login or Register  to view this content.

Also, I took all the coloring code out since you didn't want it.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.