Excel Macro

[AdamSnow]

Hey everyone...

I've got to make an excel document, that will end up being quite large... a lot of editing, that I really don't want to do by hand... I need to make a bunch of cells that look like the following:

01-A-061001
01-A-061002
01-A-061003
all the way to
01-A-061031

Then I need to move to B, C, D, E, etc... so:

01-B-061001
01-B-061002
etc.

all the way through the alphabet...

Anyone have any advice on writing a macro or something to do this? if I have to do this by hand it's going to suck... because once I get done the 01's, I need to go all the way up to 79...

79-A-061001
to
79-Z-061001

heh...

Any advice would be great!!! Thanks so much everyone!

 

[neit]

you should be able to do some for loops in your macro, a quick google search showed an example like this:



i didn't understand exactly the formatting you were after, but simple for loops should do the trick. do some trial and error and learn something new =)

 

[Kyteland]

You can just write a spreadsheet to generate this and then copy/paste the text you want in to another sheet.

First start by a constant in cell I1. Since you want to wrap the first number at 31, I1 = 31.

Fill column A with sequential numbers, starting with 0 (0,1,2,3,4,...) and fill down to the bottom of the sheet.

Fill cell B1 with this formula: =TEXT(FLOOR(A1/I$1/26,1)+1,"00")

Fill cell C1 with this formula: =CHAR(65+MOD(FLOOR(A1/I$1,1),26))

Fill cell D1 with this formula: =61001+MOD(A1,I$1)

Fill cell E1 with this formula: =B1&"-"&C1&"-"&D1

Fill B-E down, and you have your text.

The first 50 rows as an example:

 

[AdamSnow]

That works awesome Kyteland! Only problem is that I need to have the date in the following format:

061001

I need that pre-leading 0... any suggestions on how to do that without making the field text and wrecking it?

 

[PowerEngineer]

Change the D column formula to:

=TEXT(61001+MOD(A1,I$1) ,"000000")