VBA: Writing a sub routine to sum random numbers as they are generated.

[annawilliams7227]

Hey guys,

I am trying to write a subroutine that will generate n random integers and output them one by one to consecutive cells in an empty column on my worksheet.
I am using "Inputbox" to prompt the user to enter how man numbers to generate (i.e the value of n). and I want each random interger to lie between 1 and 100.
The part i'm having trouble with is I want the value in the previous cell to be added to the new integer before teh new value is output (e.g. if value in the previous cell is 5 and the random current number is 4, the otuput value is 9)
Finally after the number generation process has occurred I want to calculate the average of those generated numbers.
This is waht i've managed to do so far (which is not much).. its probably really simple bu i'm so stuck!!

thanks for your help!!

Sub RndInt()
n = Val(InputBox("Enter number of values"))
For i = 1 To n
Range("EmptyColumn").Cells(i, 1).Formula = cumsum
cumsum = cumsum + WorksheetFunction.RandBetween(1, 100)
Next i
End Sub

 

[DonkeyOte]

Perhaps something along the lines of:

Sub RndInt()
Dim lngCount As Long, i As Long, lngSum As Long
lngCount = Application.InputBox("Enter number of values",Type:=1)
For i = 1 To lngCount Step 1
    lngSum = lngSum + WorksheetFunction.RandBetween(1,100)
    Range("EmptyColumn").Cells(i,1).Value = lngSum
Next i
End Sub

Not much different - just swap the order of calculating the cumulative total before writing the value to the cell.

EDIT: I think Peter has resolved your real question.. sorry I misread (ie prior cell only as opposed to all prior cells)

 

[VoG]

Hello and welcome to MrExcel.

Maybe something like this - the numbers are written to column A and the average is placed in B1.

Sub test()
Dim i As Integer, j As Integer
i = Application.InputBox("Enter number of values", Type:=1)
Randomize
For j = 1 To i
    If j = 1 Then
        Range("A" & j).Value = Round(Rnd() * (100 - 1) + 1, 0)
    Else
        Range("A" & j).Value = Range("A" & j - 1).Value + Round(Rnd() * (100 - 1) + 1, 0)
    End If
Next j
Range("B1").Value = WorksheetFunction.Average(Columns("A"))
End Sub

 

[annawilliams7227]

thank you! that worked perfectly