Need numbers to count up

[Larbec]

I have a 4 digit number that I want to count up as if it goes odd or even, I have been doing this by hand and would love a way to just plug in 4 digit number in excel. Here is an example

Lets say I have 3347 I want it to change the 1st and 3rd digit do I get 4347 and 3357, Then I want 4347 and 3357 to change to 4447 and 4348 which is the 2nd and 4th digit. The next set of numbers would be back to the 1st digit and 3rd .... 4447 goes to 5447 and 4457, and 4348 goes to 5348 and 4358, .

It will look like this

3347
4347
3357

then take the two NEW numbers and change the 2nd and 4th digit UP (always UP)

4347
4447
4348

3357
3457
3358

then take the NEW numbers and change the 1st and 3rd digit UP (always UP)

4447
5457 (new number)
4467 (new number)

4348
5348 (new number)
4358 (new number)

3457
4457 (new number)
3467 (new number)

3358
4358
3368 (new number)

then take the two NEW numbers and change the 2nd and 4th digit UP (always UP) and keep building and new numbers. I would like to run this out to make at least 20 new numbers but I will take whatever I can get LOL

Thanks in advance

[tehneXus]

Hi,

is the first number always at least 1000 or could it be 0001 ?

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Larbec]

Never a "0" 1,2,3,4,5,6,7,8,9,1. Sorry I should have explained that. So, if we have 9999 it would goto 9199 or 9991 Or 1999 or 9919

I hope this makes sense

[tehneXus]

Hi,

try this file: Number.xlsm
You can set the start number and the number of blocks you want, be careful with the number of blocks,...

contains:

Please Login or Register  to view this content.

[Larbec]

never a "0" numbers only go from 1-9 and start over again (1,2,3,4,5,6,7,8,9,1) so 9999 would be 1999 or 9919, OR 9199 or 9991. These ONLY count up and never down

[Larbec]

When I run this it is giving me a lot of "0"and also a 5 digit number.. It should always give a 4 digit number and never a ZERO The 9 rounds back up to and you never carry over anything like addition

[tehneXus]

Hi and sorry for the misunderstanding with the zeros, I hope I got it now

So now the code is incrementing the digit skipping the 0 and going to 1 instead: Number.xlsm

btw: in your first post, the 3rd block, shouldn't there be 5447 and 4457 after 4447 ? or did I miss something again?

Please Login or Register  to view this content.

[Larbec]

THANK YOU!! THANK YOU!! THANK YOU!!! This was exactly what I was looking for

[Larbec]

Is it possible to get one that I can put the same number in and it begin with the 2nd and 4th digit changing first

2378
2478
2379

Much apprecited

[tehneXus]

Hi,

you can hard-code it by removing the following line:

Please Login or Register  to view this content.

or you could let the user decide by replacing the line with:

Please Login or Register  to view this content.

btw: just noticed that there is alway 1 block more created then the user sets, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Larbec]

I am somewhat of newbie so sorry for asking but where is the code so I can make the changes? I want to set one up for each (the 1st and 3rd) (2nd and 4th)

[tehneXus]

Hello,

to see the code do the following:
1. Enable the developer Tab as described here: http://msdn.microsoft.com/en-us/libr.../bb608625.aspx
2. On the developer tab, click the "Visual Basic" button, its the first
3. On the now opened VBA window you have on the left side the Project explorer which has an entry for every open excel file and installed add-in
4. Expand the file which contains the code and you'll see one entry per worksheet plus one "ThisWorkbook" module and, if created, standard modules, forms and class modules.
5. Double click "Sheet1 (Sheet1)" in the attached file to see the code

I'll replace the code mentioned above: Number.xlsm

[Larbec]

Thank you so much!!!!

[Larbec]

one more question please. I would like the box to say Even for the 1st and 3rd digit and Odd for the 2nd and 4th. I looked in the code and changed the Yes to Even and No to Odd but can not seem to find where to make the other changes. I did find another Yes and No (VbYes and VbNo) but when I try to change that, it gives me an error code.

[tehneXus]

Hi,

try this: Number.xlsm

As you cannot specify the text on buttons of a msgbox I've prepared a UserForm.

[Larbec]

almost there. They need to be switched Even 1st and 3rd) for Odd (2nd and 4th)

[Larbec]

Do you mind looking at my other post and see if you can help me? Only if you have time and I am not in any rush. You are amazing!!!

http://www.excelforum.com/excel-prog...20#post3276820

[jeffreybrown]

Hi Larbec,

As per Forum Rule #12, please limit quotes to only that which is necessary -- otherwise it's just clutter...Thanks.

[Larbec]

sorry. please delete the last comment about looking at my other issue

Hi Larbec,

As per Forum Rule #12, please limit quotes to only that which is necessary -- otherwise it's just clutter...Thanks.

[Larbec]

Are you able to change this or tell me how to? They need to be switched Even 1st and 3rd) for Odd (2nd and 4th)

Hi,

try this: Attachment 242687

As you cannot specify the text on buttons of a msgbox I've prepared a UserForm.

[tehneXus]

Hi

if you want to edit the layout of the form double click it in the Project Explorer of the VBA Window

Number.xlsm