I need help for a Random Number Generator

[xr600rr]

Hello, I need some help. What I need is a random number generator for numbers 1-9. It needs: 1 number per cell in a column and the numbers can not be the same. i.e.:
4
1
2
6
8
5
3
7
9

I have a VB mod that will produce the numbers but all the numbers appear in the same cell:

Function RandLotto(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
RandLotto = RandLotto & " " & iArr(i)
Next i
RandLotto = Trim(RandLotto)
End Function

With =randlotto(1,9,1) as the formula for the cell.

Any Ideas?

 

[GlennUK]

Why don't you use your VBA Function in a cell, then use formulae to extract the numbers into separate cells.

For example, if the formula =RandLotto(1,9,9) is in cell A1, then use this in cell A2 :
=MID(A1,1,1)
in A3 =MID(A1,3,1)
in A4 =MID(A1,5,1)
etc etc


Glenn.

 

[xr600rr]

Outstanding! Thank you!

 

[xr600rr]

Well, Theres a new problem now. My vlookup does not work when the cell with the =mid ref in it.

=VLOOKUP(1,B14:D22,3,FALSE)

b14 is where the =MID(B39,3,1) is and now on the vlookup cell is #N/A???

 

[GlennUK]

Ah, you didn't say that you wanted to use them as numbers. Simply change the formula to be =VALUE(MID(B39,3,1)) and it will work.


Glenn.

 

[xr600rr]

its still coming up as #N/A?

 

[tusharm]

Did you do the VALUE() stuff for all three formulas?

For other ways of solving the problem, including non-programmatic solutions, check the Excel | Tutorials | Random Selection page of my web site.

 

[xr600rr]

yes i did. still coming up with na...

 

[Mark O'Brien]

I'm no VLOOKUP expert, and forgive me for butting in, but I'd guess that your lookup table hasn't been sorted, would something like this work?

=VLOOKUP(1,B14:D22,3,TRUE)

 

[Mark W.]

I'm no VLOOKUP expert, and forgive me for butting in, but I'd guess that your lookup table hasn't been sorted, would something like this work?

=VLOOKUP(1,B14:D22,3,TRUE)

Mark, TRUE is the range_lookup value that requires the table_array to be sorted on its leftmost column -- not FALSE.