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.
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.