Tutorial for populating a 100x100 array with a random number between 1 and X

[Darth269]

Hi there all,

I'm looking to see if anyone can point me towards a tutorial or set of tutorials that would help me create and populate a vba 100x100 array with a random whole number between 1 and X, where X is likely 32 or somewhere near it, but I've not yet finalised.

The answer to this would be lovely, but I think i'd learn more if anyone could guide me.... I've searched a number of tutorials on arrays, and will keep doing so, but being completely new to arrays, it's tough going and I wondered if anyone knows of any particularly good tutorials...


Thanks in advance...

Darth

[zbor]

Use

=RANDBETWEEN(1, 32)

and pull formula to fill all array.

Then copy -> paste as values if you don't wan them to change.

[Darth269]

Use

=RANDBETWEEN(1, 32)

and pull formula to fill all array.

Then copy -> paste as values if you don't wan them to change.

Hi there and thanks for the reply... however unless i'm missing something, this doesn't help with a VBA Array... I guess I should have specified, but I thought using the term Array, and posting in the Macro forum would make it clear. I do appreciate the response though, and don't want to sound ungrateful, this just isn't what I'm looking for.

[zbor]

People often put question in a wrong subforum so it's nice to write desired solution

Here, try this:

Sub Random_Array()

Set Rng = Range("A1")
For i = 1 To 100
    For j = 1 To 100
        Rng.Offset(i - 1, j - 1).Value = 1 + Int(32 * Rnd())
    Next j
Next i
End Sub

[bakerman2]

Create Array in memory and write to worksheet in 1 go.

Sub Random_Array()
Dim square()
numrows = 20: numcols = 10
ReDim square(1 To numrows, 1 To numcols)
For i = 1 To numrows
    For j = 1 To numcols
        square(i, j) = 1 + Int(32 * Rnd())
    Next j
Next i
With Range("A1")
    .CurrentRegion.ClearContents
    .Resize(numrows, numcols) = square
End With
End Sub

[Darth269]

Create Array in memory and write to worksheet in 1 go.

Sub Random_Array()
Dim square()
numrows = 20: numcols = 10
ReDim square(1 To numrows, 1 To numcols)
For i = 1 To numrows
    For j = 1 To numcols
        square(i, j) = 1 + Int(32 * Rnd())
    Next j
Next i
With Range("A1")
    .CurrentRegion.ClearContents
    .Resize(numrows, numcols) = square
End With
End Sub

Hi there Bakerman2,

thanks for that, I think i've come up with almost what you've written, but i'm getting an error when running, can you spot where I've gone wrong?

Sub MAP_CREATOR()

Dim MapArray() As Byte
Dim SysType As Byte
Dim TheRange As Range
Dim CellsDown As Long, CellsAcross As Long


With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

'   Change these values
CellsDown = 5
CellsAcross = 5

'   Redimension temporary array
ReDim MapArray(1 To CellsDown, 1 To CellsAcross)
 
'   Set worksheet range
Set TheRange = Range(Cells(1, 1), Cells(CellsDown, CellsAcross))

For AR = 1 To CellsDown
    For AC = 1 To CellsAcross
        Randomize
        MapArray(AR, AC) = (Int((32 - 1 + 1) * Rnd + 1))
    Next AC
Next AR

With Range("A1")
    .CurrentRegion.ClearContents
    .Resize(CellsDown, CellsDown) = MapArray
End With

'TheRange.Value = MapArray
'Worksheets("MAP").Range("A1:CV100").Value = MapArray

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

End Sub

runtimeerr.png

[Darth269]

Oh crap... I think i've figured it, though I don't understand why...

I changed "Dim MapArray() As Byte" to Integer.... the code now works.

[bakerman2]

You're welcome and thanks for rep+.