RAND() and permanently capturing the results

[gsacorp]

Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I am currently using “=ROUND(<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace><st1:stockticker>RAND</st1:stockticker></st1lace>()*(99999999-11111111)+11111111,8)” to generate a random 8-digit number.
<o></o>

1) The formula works – Yay for me!!!
2) I have turned off the sheet’s auto-calc function and must press “F9” for a calculation – that’s fine
<o></o>

Now what I need:
a) To PERMANENTLY capture the number generated in “A1” somewhere. The captured number CAN NOT change every time I press F9 in the <st1lace><st1:stockticker>RAND</st1:stockticker></st1lace> sheet. I’d prefer it to be captured somewhere on the same sheet (i.e “D1”, etc.). Alternately, it could be captured in a new tab/sheet in the same workbook, or lastly in a new workbook.
b) To place a minus (“-“) sign between digits #4 &5 (e.g. xxxx-xxxx)
c) That once a number is used, it is disallowed from future possibilities

<o></o>
I tried using <st1lace>two <st1:stockticker>RAND</st1:stockticker></st1lace>’s with 4-digits each, but could not put them together in a single cell with the minus sign and still had the problem of the number changing.

<o></o>
The reason I need to permanently capture the number is that it will be assigned to an employee as their ID number.


Finally, I know what the words VBA stand for, but have never used it and was hoping to be able to accomplish my task with formula and function within excel.

<o></o>
TIA,

<o></o>
gsacorp

 

[GTO]

Greetings,

While I am still oft amazed at what can be done thru formula/worksheet function, other than overwriting the formula w/the value returned, you cannot really save a "first result" so-to-speak.

If VBA is not forbidden, you could use code to return a unique "number" each time it is run.

By way of example, open a new workbook, press ALT + F11. This will open VBIDE (the code window).

From the menubar, Insert | Module.

Paste the following:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> AddEmpNum()<br><SPAN style="color:#00007F">Dim</SPAN> rngFoundVal <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> rngLCell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> strEmpNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> bolFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rngLCell = Cells(Rows.Count, 1).End(xlUp).Offset(1)<br>    <br>    <SPAN style="color:#00007F">Do</SPAN><br>        strEmpNum = UniqueRand<br>        <SPAN style="color:#00007F">Set</SPAN> rngFoundVal = Range("A:A").Find(What:=strEmpNum, _<br>                                            After:=Cells(Rows.Count, 1), _<br>                                            LookIn:=xlValues, _<br>                                            LookAt:=xlWhole, _<br>                                            SearchOrder:=xlByRows, _<br>                                            SearchDirection:=xlNext)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngFoundVal <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            bolFound = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngFoundVal = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            bolFound = <SPAN style="color:#00007F">False</SPAN><br>            rngLCell = strEmpNum<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> bolFound<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br><SPAN style="color:#00007F">Function</SPAN> UniqueRand() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    Randomize<br>    UniqueRand = Format(CStr(Int((9999 - 1 + 1) * Rnd + 1)), "0000") & "-" & _<br>                 Format(CStr(Int((9999 - 1 + 1) * Rnd + 1)), "0000")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

When run, this should place a new unique value in the next available cell in Col A of the active sheet.

Hope that helps,

Mark

 

[gsacorp]

Hey GTO,

This looks scary-amazing, I'll look at it more later, but just did the cut-n-paste thing, and it seems to have been accepted.

However, now that it's in, how do I make it work???

TIA,

gsacorp

 

[Blade Hunter]

If you have definately got it set to manual just put this on the sheet level

Private Sub Worksheet_Calculate()
Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1).Formula = Range("A1").Value
End Sub

Assumes your formula is in A1 and you want the log in B

 

[gsacorp]

Hi Blade,

This looks like VB and simple - thanks. Where/how do I put it on the "sheet" level and how do I make it "work"? Remember, I only know the initials VBA, not how to use it...

TIA,

gsacorp

 

[Blade Hunter]

Right click on the sheets tab and choose "View Code", paste it in the window that comes up.

 

[GTO]

Hey GTO,

This looks scary-amazing, I'll look at it more later, but just did the cut-n-paste thing, and it seems to have been accepted.

However, now that it's in, how do I make it work???

TIA,

gsacorp

To run the Sub AddEmpNum, you can install a button on the sheet, or, you can simply press ALT + F8 and the macro dialog box will come up. Select the procedure and press the 'Run' button.

Mark