Combinations of strings - looping question

TVono

New Member
Joined
Oct 17, 2006
Messages
1
I need to start with a spreadsheat with columns that look like this:
StmtType1
weight
Marketing Page 1
Marketing Page 2
Marketing Page 3
up to
Marketing Page 15
Insert 1
Insert 2
up to
Insert 5
marketing carrier
generic carrier
marketing return
generic return

and have a macro cycle through the 8 different Types to create a 1 column table in this fashion:
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,overflow,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,marketing1,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,marketing1,insert1,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,overflow,marketing1,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,overflow,marketing1,insert1,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,marketing2,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,overflow,marketing2,carrier,return;d
a;MMMYYYY;b;c;weight;Tt99x;ZAC1D999;type,carrier;type;type,overflow,marketing2,insert1,carrier,return;d

a,b,c, & d are all constants
MMMYYYY = NOV2006
weight = 1 'usually could be 2 in the future'
weight = 1 then Type + 2 pages + 1 insert
weight = 2 then type + 3 pages + 1 insert or type + 2 pages + 2 inserts 'must be easily editable to change number of inserts per month'
Tt99x = Tt for type, 2 numerics, 1 alpha <- count of combinations
ZAC1D999 = ZAC1D + 3 numerics <- count of number of combinations per Type

I'm confident I can build the string to stick in the column on sheet 2, what I'm not sure about is how to create the different combinations of marketing, inserts, carriers, and returns. Loops are not my strong point in VB. There will always be 1 of 8 kinds of "StmtType", and there are usually just 2 kinds of carriers and 1 or 2 kinds of returns.

I also need Tt99x to increment up as in: Tt01A, Tt01B, Tt01C for each block of combinations for StmtType1, then StmtType2 would start over at Tt02A, Tt02B, etc. Then ZAC1D is the number of blocks to look like: Tt01A;ZAC1D001, Tt01B;ZAC1D001, Tt02A;ZAC1D002, Tt02B;ZAC1D002, etc.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,136,988
Messages
5,678,970
Members
419,796
Latest member
doctorgresham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top