RAND() and permanently capturing the results

gsacorp

New Member
Joined
Sep 19, 2009
Messages
6
Hi all,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
I am currently using “=ROUND(<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:place><st1:stockticker>RAND</st1:stockticker></st1:place>()*(99999999-11111111)+11111111,8)” to generate a random 8-digit number.
<o:p></o:p>

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:p></o:p>

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 <st1:place><st1:stockticker>RAND</st1:stockticker></st1:place> 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:p></o:p>
I tried using <st1:place>two <st1:stockticker>RAND</st1:stockticker></st1:place>’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:p></o:p>
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:p></o:p>
TIA,

<o:p></o:p>
gsacorp
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 
Last edited:

gsacorp

New Member
Joined
Sep 19, 2009
Messages
6
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
If you have definately got it set to manual just put this on the sheet level

Code:
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

New Member
Joined
Sep 19, 2009
Messages
6
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Right click on the sheets tab and choose "View Code", paste it in the window that comes up.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Forum statistics

Threads
1,082,065
Messages
5,362,976
Members
400,702
Latest member
oliviaalx

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top