Random number

[vincen1211]

I hv 10x10 box (a1..j10) want to fill with random number 1-100
Any idea how to make function
Thanks b4

[Steel Monkey]

you could try:
=rand()

format the cells to suit

[arthurbr]

This link should get you started

[davegugg]

I'd go with =randbetween , then you could put in the top and bottom numbers.

[shg]

If you want random, non-repeating numbers from 1-100, you can use this function:

Function RandSeq(Optional bVolatile As Boolean = False) As Long()
    ' shg 2006-0307
    ' Worksheet function *only*

    ' Returns a random sequence from 1 to N to the calling range
    ' (entered in a single cell, returns 1)

    If bVolatile Then Application.Volatile

    Dim nRow    As Long     ' count of rows
    Dim nCol    As Long     ' count of columns

    Dim afRnd() As Single   ' random rumbers
    Dim nNum    As Long     ' count of afRnd
    Dim iRnd    As Long     ' index to afRnd
    Dim jRnd    As Long     ' secondary index to afRnd

    Dim aiRank() As Long    ' rank array
    Dim iRow    As Long     ' row index to aiRank
    Dim iCol    As Long     ' column index to aiRank

    ' verify that the caller is a range
    If Not TypeOf Application.Caller Is Range Then Exit Function

    With Application.Caller
        nNum = .Count
        nRow = .Rows.Count
        nCol = .Columns.Count
    End With

    ' size arrays to size of range
    ReDim afRnd(0 To nNum - 1)
    ReDim aiRank(0 To nRow - 1, 0 To nCol - 1)

    ' get random numbers
    For iRnd = 0 To nNum - 1
        afRnd(iRnd) = Rnd
    Next iRnd

    ' rank 'em
    For iRnd = 0 To nNum - 1
        iRow = iRnd \ nCol
        iCol = iRnd Mod nCol
        For jRnd = 0 To nNum - 1
            If afRnd(iRnd) >= afRnd(jRnd) Then aiRank(iRow, iCol) = aiRank(iRow, iCol) + 1
        Next
    Next
    
    RandSeq = aiRank
End Function

... and array-enter this formula in the 10x10 range:

=RandSeq()

[vincen1211]

@all thanks..
Special to arthurbr for link, teach me array-enter and shg for the function..

[shg]

Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

If more than two days have elapsed since your first post, ask a moderator to mark it.

[vincen1211]

Done, Thanks !!!