VBA Array Sum Value Each Iteration

[qeni]

Hi guys,

I'm fairly new to VBA and I'm having a hard time with how Excel handles arrays. I'm generating a random number of integers from 1 to 10, iterating 10 times. I can't seem to figure out how to sum each iteration and place the value in a new array. Here's the code I have so far:

Sub getRandom()
    Dim i As Integer, r As Integer
    Dim minV As Integer, maxV As Integer
    Dim size As Integer, iterate As Integer
    Dim sumAll As Long
    Dim myArray() As Variant
    
    Cells.Clear
    
    minV = 1200
    maxV = 1800
    size = 10
    iterate = 10
    
    
    For r = 1 To iterate
    sumAll = 0
        For i = 1 To size
            ReDim Preserve myArray(1 To i)
            myArray(i) = Int(((maxV - minV) + 1) * Rnd + minV)
            Cells(i + 5, r) = myArray(i)
        Next i
    Next r
    
End Sub

Any help would be appreciated.

 

[Scott Huish]

After running your code, this is what I got:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
6	1624	1227	1515	1698	1263	1377	1446	1744	1537	1508
7	1520	1448	1661	1695	1800	1429	1448	1357	1617	1478
8	1548	1718	1232	1554	1606	1380	1628	1671	1749	1412
9	1374	1675	1556	1792	1209	1770	1396	1427	1701	1443
10	1381	1424	1481	1747	1545	1788	1580	1374	1213	1362
11	1665	1778	1379	1336	1260	1441	1324	1752	1526	1233
12	1208	1723	1574	1617	1261	1367	1311	1579	1750	1346
13	1657	1233	1589	1788	1680	1296	1550	1577	1458	1788
14	1689	1770	1358	1346	1370	1297	1248	1457	1607	1236
15	1626	1418	1367	1520	1227	1588	1475	1258	1501	1434
Sheet1

Although I don't see why you couldn't jut put formulas in the cell and convert to values.

Did you want a sum of each row?

 

[Scott Huish]

This should do what your code does:

Sub getRandom()
Dim minV As Long, maxV As Long
Dim size As Long, iterate As Long
    
Cells.Clear
minV = 1200
maxV = 1800
size = 10
iterate = 10
With Range("A6").Resize(size, iterate)
    .Formula = "=Int(" & maxV - minV + 1 & " * Rand() + " & minV & ")"
    .Value = .Value
End With
End Sub

 

[qeni]

@ HOTPEPPER : Thanks for your reply. To answer your question, I am trying to store the sums of each column into a one dimensional array. In other words as I iterate each i calculation r times, the sum of each iteration r is stored as a value in an array.

I really don't need to display any values other than the sums of each i. I Googled VBA arrays but I'm getting a little lost. I think I am making this more complicated than it needs to be.