"Wendy" skrev i en meddelelse
...
There is a function that allows you to select Random Numbers
=randbetween(1,25). This function is available when you add in the
Analysis
Toolpak. The problem is that if I copy it over cells it does sometimes
repeat a number. I only want unique numbers can anyone help with this
one,
maybe there is another way of getting only unique numbers to appear
--
Wendy

Hi Wendy

Here's an UDF(UserDefinedFunction).
If you select 25 cells the numbers 1 through 25 are inserted
at random in the cells. If you select 15 cells, numbers 1 -15
are inserted etc.
From the workbook this is accomplished thus:

1. Select the range
2. While the cells are selected enter the formula
=myrand()
3. Finish with <Shift<Ctrl<Enter instead of just <Enter

If you want to start with the number 10 instead of 1 enter

=myrand(10)

to start with -10 instead of 1 enter

=myrand(-10)

etc.

To get a new set of values press <Ctrl<Alt<F9.


Insert the below function in a general module.


Function MyRand(Optional RandOffset As Long) As Variant
'Leo Heuser, 2 August, 2006
Dim Counter As Long
Dim Counter1 As Long
Dim NumOfElements As Long
Dim Placement As Long
Dim Result() As Variant
Dim RandColumns As Long
Dim RandData() As Variant
Dim RandRows As Long

Randomize

With Range(Application.Caller.Address)
RandRows = .Rows.Count
RandColumns = .Columns.Count
End With

NumOfElements = RandRows * RandColumns

If RandOffset = 0 Then RandOffset = 1

ReDim RandData(1 To NumOfElements)

For Counter = 1 To NumOfElements
RandData(Counter) = Counter + (RandOffset - 1)
Next Counter

ReDim Result(1 To RandRows, 1 To RandColumns)

For Counter = 1 To RandRows
For Counter1 = 1 To RandColumns
Placement = Int(Rnd() * NumOfElements + 1)
Result(Counter, Counter1) = RandData(Placement)
RandData(Placement) = RandData(NumOfElements)
NumOfElements = NumOfElements - 1
Next Counter1
Next Counter

MyRand = Result

End Function



--
Best regards
Leo Heuser

Followup to newsgroup only please.