VBA Function to explode string

T

tomjermy

Hi.

I am wanting to know hbow I would go about getting the following 3
cells:

Cells A1, A2, A3:
::
1-4,6,8 50 S1B1::

Into a form that looks more like this
::A1 A2 A3
1 8.33 S1B1
2 8.33 S1B1
3 8.33 S1B1
4 8.33 S1B1
6 8.33 S1B1
8 8.35 S1B1::

How it does the rounding (and on which one), it does not matter!

Thanks,
Tom
 
B

Bob Phillips

You haven't explained the rules of how you get from what you have to what
you want.
 
T

tomjermy

Sorry, I make this clearer:

What I want to do is have cells where users enter data as so:

CELL A1: 2-3,6
CELL B1: 40
CELL C1: S1B1

Users can enter data in this format into columns (ABC), on multiple
rows.

From this data, I would like to write a script which takes the first
line, and explodes CELL A1 into:

CELL D1: 2
CELL D2: 3
CELL D3: 6

Then take the number in CELL B1 ($40) and split that amount over the 3
cells, to 2 decimal places, rounded so that the values equal the $40
entered to B2. This goes into cells E1:E3:

CELL E1: $16.66
CELL E2: $16.66
CELL E3: $16.68

Then take CELL C1 and show this value in CELL F1, F2, F3.

As mentioned at thje beginning, the idea is to allow many entries into
the rows of columns ABC, and perform this proceedure on all of them.

In summary, I want to go from:
2-3,6 40 S1B1
1-4 40 S5J1
to

2 16.66 S1B1
3 16.66 S1B1
6 16.68 S1B1
1 10 S5J1
2 10 S5J1
3 10 S5J1
4 10 S5J1

If someone can help me with all or even part of this, that would be
great!

Thanks,
Tom
 
Top