help for Random no

[jinvictor]

what i want is:

1. column B4 - B150 are random nos between 1-16.
2. column D4 - D15 are random no between 1-16 but dont have No. 2,5,9,11

any body can help above 2 questions?

thanx

[Carim]

Hi,

As far as random is concerned, take a look at

http://www.mcgimpsey.com/excel/udfs/randint.html

you will random without duplicates, but random between a range with exclusion, I do not think so ...

HTH
Carim

[mudraker]

Try this Macro & function - you need both

Sub FillRandomNumbers()
Dim rNG As Range
Dim iNum As Integer
For Each rNG In Range("b4:b150")
rNG.Value = RandomNumbersGenerator(1, 16, 0)
Next rNG
For Each rNG In Range("d4:d15")
Do
iNum = RandomNumbersGenerator(1, 16, 0)
Select Case iNum
Case 1, 2 To 4, 6 To 8, 10, 12 To 16
rNG.Value = iNum
Exit Do
End Select
Loop
Next rNG
End Sub


Public Function RandomNumbersGenerator(Lowest As Long, _
Highest As Long, Optional Decimals As Integer)
Application.Volatile 'Remove this line to "freeze" the numbers
If IsMissing(Decimals) Or Decimals = 0 Then
Randomize
RandomNumbersGenerator = Int((Highest + 1 - Lowest) * Rnd + Lowest)
Else
Randomize
RandomNumbersGenerator = Round((Highest - Lowest) * Rnd + Lowest, Decimals)
End If
End Function

[dominicb]

Hi

Try this custom function :

Please Login or Register  to view this content.

Then, when you use the formula =RandyMan() in your worksheet a random number will be generated excluding 2,5,9 and 11.

HTH

DominicB

EDIT : Are we bothered about duplicates here or what ... ?

[Carim]

Hi Mudraker ...

I like your code very much ...
Had not experienced this issue before ...

Cheers
Carim

[Carim]

Hi DominicB,

Your Function is Ultra **** ...
I am storing it right away ...

Cheers
Carim

[dominicb]

Hey Carim

****!?!?!?!?!?!?!?!

Are you a robot, or do you just need to get out more

DominicB

[Carim]

Aren't we both in LOVE with XL ... !!!