Random numbers in MS Excel

[davehill1974]

Hi everyone,
I have been trying repeatedly for days now to write a macro to generate random numbers. I need to generate 23 unique numbers in 5 columns. I have found a macro that can do one column at a time but i cannot modify this to work over the five columns.

Here is the code for you to look at:

Sub CreateRand()
Dim Nums(23)
Dim I As Integer
Dim X As Integer
Dim rng As Range
Dim c As Range
Dim Filled As Boolean

For I = 1 To 23
Nums(I) = I
Next I

Set rng = Range("f1:f23")

For Each c In rng

Do

X = Int((Rnd * 23) + 1)
If Nums(X) <> 0 Then
c.Value = Nums(X)
Nums(X) = 0
Filled = True
End If
Loop Until Filled
Filled = False
Next c
End Sub

All help will be greatly received!!!!!!


Thanks, a slowly going bald Excel user.

 

[TonyJollans]

Nothing to do with random numbers at all ..

Set rng = Range("[red]f1:f23[/red]")
     
For Each c In rng
    :
    : etc.

will do whatever you tell it to, for each cell in the range. If you want to work on a different range, say so ..

Set rng = Range("[red]f1:i5,j1:j3[/red]") ' Perhaps
     
For Each c In rng
    :
    : etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[davehill1974]

Hey thanks i will go and try it now!

 

[PHV]

Something like this ?
For Each col in Array("f", "g", "h", "i", "j")
For I = 1 To 23
Nums(I) = I
Next I
Set rng = Range(col & "1:" & col & "23")
For Each c In rng
...
Next c
Next col

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[davehill1974]

Thanks PHV It's so obvious when oyu see it!!!!

My hair can go back to its natural colour now.