[b]Creating a unique number cell[/b]

[Shalls]

I am trying to add a cell that will generate a unique (order) number each time the template is open. I am a new user to Xcel and am stubbling my way through.

 

[phantom1975]

WELCOME TO THE BOARD!

There are two ways that you can generate a random number. The first is with a formula:

Book1
	A	B	C	D
1	0.621347	0.579519
2	6
3	62
4	621
5	6213
6	62134
Sheet1

The problem that you might have with this approach is that the random number changes every time you type anything.

The second approach is with a macro. This macro will create a random number every time the worksheet is activated.<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Activate()

    Range("B1").Value = Rnd()<SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Tom Urtis]

Just to clarify a point, as unlikely as it is, the Random solution might generate 2 identical numbers over the lifetime of your template unless further VBA instructions are imposed. If you really want a unique random number, you'll need to keep track of all the random numbers generated at Open, and then generate a random number that you can confidently assign, once it is determined that such number does not exist in the population of previously generated random numbers (hence unique). The code is not complicated and it's all possible with VBA as Phantom points out, just more involved than the above macro, which is missing non-duplicate checking that would guarantee a true unique random number.

 

[Shalls]

Thanks for the info. I think I am almost there. Like I said I am pretty stupid when it comes to Xcel. Could you explain the "macro" stuff alittle more? Do I put the cell I want the info in where the "B1" is?

 

[Tom Urtis]

Do I put the cell I want the info in where the "B1" is?

Yes, if B1 is your destination cell of the sheet you are activating.

 

[phantom1975]

Thanks for the info. I think I am almost there. Like I said I am pretty stupid when it comes to Xcel. Could you explain the "macro" stuff alittle more? Do I put the cell I want the info in where the "B1" is?

I guess the best way to explain a macro is to encourage you to record a macro. From the main menu, select TOOLS....MACRO.....RECORD NEW MACRO. You will be prompted to name the macro and then start recording. On sheet1 type the word "Phantom" in cell A1. Then type the work "smells funky" in cell A2. Stop recording the macro. Now go to sheet2 and run the macro you just recorded. TADA! You will notice that A1 and A2 will have Phantom smells funky.

Push ALT+F11 and you will see the Visual Basic code that was written by the macro recorder. Once you learn the language, you can create a macro on your own.