Random Numbers

**kAustin79** · 11-08-2007, 11:01 PM

I have a sheet with 11 columns and 11 rows. Each cell has in it this formula: =RANDOM()*99.

I need every cell to be filled with a random, two digit number. I also want to somehow make sure that there are no adjacent cells with the same number.

Can someone help me with this?

**mikerickson** · 11-08-2007, 11:37 PM

How about a UDF. Put =FancyRand() in each of the cells. It will take a bit to settle down, but I think it will do what you want. You may want to set Calculation to manual.

Function fancyRand(Optional ByVal inRay As Range) As Double
Dim Neighbors As Range, badVal As Boolean, oneCell As Range
If inRay Is Nothing Then Set inRay = Application.Caller
With inRay
    Set Neighbors = Application.Union(.Offset(1, 0), .Offset(1, 1), .Offset(0, 1))
    If inRay.Column > 1 Then
        Set Neighbors = Application.Union(Neighbors, .Offset(0, -1), .Offset(1, -1))
        If 1 < .Row Then
            Set Neighbors = Application.Union(Neighbors, .Offset(-1, -1), .Offset(-1, 0), .Offset(-1, 1))
        End If
    ElseIf 1 < .Row Then
        Set Neighbors = Application.Union(Neighbors, .Offset(-1, 0), .Offset(-1, 1))
    End If
End With
Randomize
fancyRand = Int(90 * Rnd()) + 10
For Each oneCell In Neighbors
    badVal = badVal Or (oneCell.Value = fancyRand)
Next oneCell
If badVal Then fancyRand = fancyRand(inRay)
End Function

**Myles** · 11-12-2007, 12:19 AM

Austin,

See also:

Sub TwoDigitRandNosNotAdjacent()

Dim i As Integer, j As Integer
Application.Calculation = xlCalculationManual
For i = 1 To 11
For j = 1 To 11
If IsEmpty(Cells(1, 1)) Then
Cells(1, 1) = Int(10 + Rnd * 90)
End If
Do Until Cells(i, j) <> Cells(i, j).Offset(0, 1) Or Cells(i, j) <> Cells(i, j).Offset(1, 0)
Cells(i, j).Value = Int(10 + Rnd * 90)
Loop
Next
Next
Application.Calculation = xlCalculationManual
End Sub

**Myles** · 11-12-2007, 08:17 PM

Austin,

Disregard my original code. I cobbled it together in haste and allowed for only 2 criteria circumspection checks instead of four. The following fixes the problem.

Sub AdjacentUniqueRandNos()
MsgBox "No 2 adjacent cells are equal" & vbCrLf & "but duplicates may exist in the Range"
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
End With

Range("A1:L12").Clear

Rows(1).Insert
Columns("a").Insert

For i = 2 To 12
    For j = 2 To 12
        Do Until Cells(i, j) <> Cells(i, j).Offset(0, 1) And Cells(i, j) <> Cells(i, j).Offset(1, 0) And Cells(i, j) <> Cells(i, j).Offset(-1, 0) And Cells(i, j) <> Cells(i, j).Offset(0, -1)
          Randomize
          Cells(i, j) = Int(10 + Rnd * 90)
        Loop
    Next
Next

Rows(1).Delete
Columns("a").Delete


With Application
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
End Sub

[By the way, if you want 2 digit numbers in every cell, the Worksheet formula =RAND*99 may yield single digit numbers 0 to 9 as wells as 10 to 98. Note that, 99 will be excluded as the RAND() function has the range 0 to less than 1 . The correct WorkSheet formula should be Int(10+Rand()*90) and in VBA Int(10+Rnd*90) or if you have AnalysisToolPack installed =RANDBETWEEN(10 ,99).

**lecxe** · 11-13-2007, 07:34 AM

Hi

This is a formula solution.

Leave Row 1 and Column A empty (or you can write text, but not numbers).

In B2:

=SMALL(IF((ROW($10:$99)<>A1)*(ROW($10:$99)<>B1)*(ROW($10:$99)<>A2),ROW($10:$99)),1+INT((90-IF((ROW(B2)=ROW($B$2))*(COLUMN(B2)=COLUMN($B$2)),0,IF((ROW(B2)=ROW($B$2))+(COLUMN(B2)=COLUMN($B$2)),1,3)))*RAND()))

Copy down and accross

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

Remark: You say that the cells must have 2 digit numbers. I interpreted it as numbers from 10 till 99.

HTH
lecxe

Random Numbers

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Random Numbers

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions