VBA to create random number block.

[Rudi]

Hi all,

I need help creating some code that will fill cells with specific random numbers (no decimals).
LOW = Numbers between 0 and 10
HIGH = Numbers between 11 and 15

I need a 10 x 10 block of cells where each row will have 6 cells of LOW numbers and 4 cells of HIGH numbers in any random order
The macro will then continue to fill the block with each row containing any arrangement of 6 LOW and 4 HIGH numbers until the 10 rows are complete.

Any help will be great TX

PS: I started a macro but I am hitting a blockage... :)

Sub RandomBlock()
Dim myLow, myHigh As Byte
Dim c, r, myRow As Integer
myRow = InputBox("Type number of rows", "Number of rows", 10)

For r = 1 To myRow
For c = 1 To 10
myLow = Round(Rnd * 10, 0)
Cells(r, c).Value = myLow
Next c
Next r
'I dunno how to create myHigh??? I wish VBA had RANDBETWEEN :)
End Sub

[Leif]

'I dunno how to create myHigh??? I wish VBA had RANDBETWEEN :)

Can you create a random number between 0 and 4 and then simply add 11 to it?

[HansV]

Here is a specific random number: 37

But seriously, to generate a random whole number between L and H, you can use

L + Int(Rnd * (H - L + 1))

I wouldn't use Round here: the value 10 would occur only half as frequently as the value 9, for all values of Rnd * 10 between 8.5 and 9.5 would be rounded to 9, but only those between 9.5 and 10.0 to 10.

[Rudi]

Here is a specific random number: 37

Thanks Hans...I'll try that formula.

The specific was used to refer to numbers in a specific range :) But I understand your evilgrin...it sounds weird I suppose!

Thanks Leif...Now that you mention, that is pretty obvious

[Rudi]

I tried the formula Hans...and the numbers are being created nicely.

Now the part that is confusing is how do I get it to loop through the 100 cells and ensure that I get 6 LOW and 4 HIGH numbers in each of the 10 rows?

Any ideas?

[HansV]

I'd create an array of ten items.

Repeat the following steps for each of the ten rows:

1. Fill the first six items of the array with random numbers between 0 and 10.
2. Fill the last four items with random numbers between 11 and 15.
3. Shuffle the items of the array.
4. Write the values of the array into the row.

To shuffle an array, you can use this procedure:

Code: Select all

Sub Shuffle(arr)
  Dim i As Long
  Dim j As Long
  Dim L As Long
  Dim H As Long
  Dim varTemp As Variant
  Randomize
  L = LBound(arr)
  H = UBound(arr)
  For i = L To H
    j = L + Int(Rnd * (H - L + 1))
    If j <> i Then
      varTemp = arr(i)
      arr(i) = arr(j)
      arr(j) = varTemp
    End If
  Next i
End Sub

If you have an array arrValues, shuffle it with the line

Call Shuffle(arrValues)

[Rudi]

Hans,

Thanks...but what you have given is Greek to me? I also have no experience with arrays, so I need to call on your assistance again.

In VBA help I found some guidance to populate arrays and have designed this...however its a far cry from doing what I need it to do.

Sub TestArray()
Dim arrValues(1 To 10, 1 To 10) As Integer
Dim r, c As Integer
For r = 1 To 10
For c = 1 To 10
arrValues(r, c) = 0 + Int(Rnd * (10 - 0 + 1)) ' + 10 + Int(Rnd * (15 - 10 + 1))
Cells(r, c) = arrValues(r, c)
Next c
Next r

Call Shuffle(arrValues)

End Sub


Please help again...

[HansV]

Try this:

Code: Select all

Sub TestArray()
  Dim arrValues(1 To 10) As Long
  Dim r As Long
  Dim i As Long
  ' Loop through rows
  For r = 1 To 10
    ' Fill low values
    For i = 1 To 6
      arrValues(i) = Int(11 * Rnd)
    Next i
    ' Fill high values
    For i = 7 To 10
      arrValues(i) = 11 + Int(5 * Rnd)
    Next i
    ' Shuffle values
    Call Shuffle(arrValues)
    ' Populate cells
    Range("A" & r & ":J" & r) = arrValues
  Next r
End Sub

[Rudi]

Hans...Many Tx

I would not have gotten this right myself.

Cheers