I am new to this site, I just signed up today. If I am not following correct protocol please let me know. I have played with VB code in Access and have done a very little in Excel. My question will definitely require code. I'd attach an example spreadsheet but I'm not sure how. Thanks in advance for any and all assistance. I'll start with a simple example as it will then be easier to frame my question.
reference designator description quantity price
Row 1: c1,c2,c3,c8,c11 capacitor 5 $0.12
Row 2: r5,r6,r10 resistor 3 $0.40
I need to break out each reference designator into it's own line. Therefore, the code needs to look at the quantity cell, subtract 1 and insert that many lines directly below the line being evaluated. Next it needs to copy the entire contents of that line into all the blank lines. It then needs to parse the reference designators so each one is on a unique line. Lastly, it should change the quantity cell to 1 for each item. I have no clue if this is the proper sequence, I'm just logically thinking of what needs to occur. The end results of this example is as follows:
reference designator description quantity price
Row 1: c1 capacitor 1 $0.12
Row 2: c2 capacitor 1 $0.12
Row 3: c3 capacitor 1 $0.12
Row 4: c8 capacitor 1 $0.12
Row 5: c11 capacitor 1 $0.12
Row 6: r5 resistor 1 $0.40
Row 7: r6 resistor 1 $0.40
Row 8: r10 resistor 1 $0.40
In this example the delimiter in the reference designator field is always a comma and it separates one reference designator. I'd be thrilled if I can get code that will work with that format. I know that I may see one additional format in the reference designator field. That would be a combination of hyphens and commas. I may see something like: r1-r4,r9 which = r1,r2,r3,r4,r9. I have no idea how difficult it would be to write code to handle both scenarios (I'm sure I will see cases where both configurations appear in one spreadsheet - see below)
reference designator description quantity price
Row 1: c1,c5 capacitor 2 $0.12
Row 2: r1-r4, r9 resister 5 $0.40
Again, thanks in advance for you help.