getting excell to sellect ramdomly between two numbers

[tonylpcs@yahoo.co.uk]

Hi Everyone,
hope someone can help.

in cell b2 i have a low number (i.e 1) and in cell b3 a high number (i.e 10)
this is the lowest and highest i want a number to be in any cell im using,

i need to be able to go to a cell say d4 for example and put in a forumal to get it to randomly select a number between b2 and b3
(if its easyier i can just run a line of numbers going from B2 across so b2=1 b3=2 and so on.

can anyone help please?

thanks

Tony

 

[ADAMC]

You can probably use a formula for this which is already built into excel (Lookup randbetween in the help files)

In B2 you have your low number
In B3 your high number
In B4 (Or wherever) you could use the formula:

=RANDBETWEEN(B2,B3)

You can select another random number between your low and high by pressing F9 (This recalculates the formula in your sheet.

Hope this helps

 

[tonylpcs@yahoo.co.uk]

Hi Adam,

thanks again for your help,

just realised on problem the random number must not be the same number as already selected by another cell (say any cell in range B5 to D10) for example? so can i do this?

Tony

 

[ADAMC]

Ive struggled with this in the past not sure there is an easy way to do it.....

Are you just creating 10 random numbers between 1 and 10?

try this macro:

Sub generate()
Dim MyMax As Long
MyMax = 10 'Change max value to suit
Dim FillRange As Range
Set FillRange = Range("A1:a10") 'change range to suit
For Each c In FillRange
Do
c.Value = Int((MyMax * Rnd) + 1)
Loop Until WorksheetFunction.CountIf(FillRange, c.Value) < 2
Next
End Sub

Or have a look at the UDF explanations found here:

http://www.cpearson.com/excel/RandomNumbers.aspx

 

[tonylpcs@yahoo.co.uk]

Thanks Adam,

we are nearly there, the problem is I cant generate the same random number again or it will create a clash

is there anyway your code could be done to randomly pick from a vaule from a set of cells?

for instance:

i could do cell

A1 A2 A7 A5 A9
and i could put the numbers i want it to chose from in these cells but they wont be in order i.e a1 might be 10 a2 might be 5 etc and there might be no 4 or six to chose from so it would have to only chose from the numbers it has in the cells?

can this be done?