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

A

annawilliams7227

New Member
Joined
Aug 16, 2009
Messages
14
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
 
Sort by date Sort by votes
Perhaps something along the lines of:

Code: 
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)
 
Last edited:
Upvote 0
Hello and welcome to MrExcel.

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

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
"Standard" lists of "random" numbers
Replies
5
Views
236
StephenCrump
S
VinceF
  • Solved
Modify Rand# Generator VBA
Replies
4
Views
143
Joe4
Joe4
A
  • Question Question
Random Number Generator That Can Exclude Numbers
Replies
2
Views
820
FormulasOnly
F
M
  • Solved
seed random number in VBA
Replies
4
Views
582
MetLife
M
pcc
  • Question Question
Can anyone explain this behaviour?
Replies
5
Views
423
Jeffrey Mahoney
Jeffrey Mahoney

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top