Random numbers without a macro?

I need to figure a way to make say cells A1 thru A10 produce random numbers from 0 to 9.

I would like to do it without a macro if possible. I also need a button to generate those random numbers. I know this button may make it impossible to not use a macro, but let's see if someone can. If it has to be done with a macro then so be it. I would appreciate it if someone could help me out.

Hi cat3rn,

You are correct - as soon as you ask for a button, you are involving macros.

RANDBETWEEN(0,9) will give you random numbers, but it is volatile, and it won't only use each number once. In other words, it is likely that you will get the same number used more than once in A1:A10. (Volatile means that it recalculates every time the worksheet calculates - that is almost every time you enter something!)

Is that what you want, or are you trying to get the digits 0 through 9 in a different order every time?

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Cross Post:

http://www.mrexcel.com/forum/excel-q...out-macro.html

I am so sorry maybe I should have been more clear.

I need to have cells A1 thru A 10 generate a random set of numbers each cell being a different number than any other in the range of cells.

Originally Posted by popipipo

Or use 2 functions in 2 different columns RAND() and RANK()

I like this but need 0 thru 9 instead of 1 thru 10

Originally Posted by popipipo

Not so difficult I think
=RANK(A1,$A$1:$A$10)-1

Great that works. Now I need to keep the values locked and not have them change anytime I input a value into another cell anywhere on the sheet.

Originally Posted by benishiryo

hi cat3rn. you're right. it needs a macro if you want the button. Otherwise, you could give the user a simple instruction to generate them by pressing F9. F9 is to calculate. the numbers will change when done so. the formulas in A1 would be:
=RANDBETWEEN(0,9)

if you need the button,

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

I like this Macro. How would I make it randomize the numbers in certain cells like E4 to N4 and D5 to D14 only.

I found out how to do something on my own. woo hoo I'm getting smarter by the year. lol On the Formulas tab > select Calculation > Select the Manual Radio Button. This makes all random numbers stay the same when inputting other data into the worksheet. I then recorded a Macro using just the F9 key. Assigned that macro to the button and WAH LAH the random numbers only change when I click the button.