Problem with RNG Macro

[Harry]

XL 2002

I often use this macro in several cells to generate random numbers:-
*-----------------------------------------------------------------*
Sub UniqueNums()
Function RandNums(Bottom As Integer, Top As Integer, _
Amount As Integer) As String
Dim iArr As Variant
Dim i As Integer
Dim r As Integer
Dim temp As Integer

Application.Volatile

ReDim iArr(Bottom To Top)
For i = Bottom To Top
iArr(i) = i
Next i
For i = Top To Bottom + 1 Step -1
r = Int(Rnd() * (i - Bottom + 1)) + Bottom
temp = iArr(r)
iArr(r) = iArr(i)
iArr(i) = temp
Next i
For i = Bottom To Bottom + Amount - 1
RandNums = RandNums & " " & iArr(i)
Next i
RandNums = Trim(RandNums)
End Function
*-----------------------------------------------------------*

In another macro I do a recalc. of the sheet until a certain parametre is
met.

Often, if I need to stop/interrupt this second macro (e.g. to debug) some or
all of the cells containing the RandNums function become *#Value* i.e. they
*lose* their original *=RandNums(x,y,z)* value.

Is there any way to correct this?

TIA

Harry

 

[Tom Ogilvy]

Possibly don't make it application.Volatile

 

[Harry]

Hello Tom and thanks for replying.
Removing Application.Volatile freezes the cells to the initial results i.e.
the recalc in the second macro has no effect.

BTW do you know of any similar macros that will give reasonable random
results without the same problem?
I'm not looking for anything *Hi Tech* here - basically I just need to
generate about 30 rows of [exactly] 50 random integers in the range 1 - 500
but without repeats . That is to say: ( i ) no two rows can be the same and
( ii ) no repeats of consecutive numbers between any one row and another
e.g. I need to avoid a situation such as :-

Row 1 = 405 | 23 | 79 | 329 |
218|..........................................
Row 2 = 56 | 399 | 95 | 79 | 329 | 23 | 405
.........................................

i.e. to avoid a repeat of 79 & 329 and/or 23 & 405 (albeit reversed)

Thanks
Harry