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

Shalls

New Member
Joined
Feb 19, 2004
Messages
2
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. :oops:
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

There are two ways that you can generate a random number. The first is with a formula:
Book1
ABCD
10.6213470.579519
26
362
4621
56213
662134
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

New Member
Joined
Feb 19, 2004
Messages
2
Thanks for the info. I think I am almost there. Like I said I am pretty stupid when it comes to Xcel. :biggrin: Could you explain the "macro" stuff alittle more? Do I put the cell I want the info in where the "B1" is?
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Shalls said:
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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Shalls said:
Thanks for the info. I think I am almost there. Like I said I am pretty stupid when it comes to Xcel. :biggrin: 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,520
Members
425,479
Latest member
Neerajcool

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