A simple loop problem

[djpeter]

I am currently trying to generate random numbers in a row. I would like to loop the following code so it takes the previous number and uses it for the mean.

'first d10 is the output range, the second 1 is for the number of random numbers generated
'the rnd() is for a random seed, the c10 is the past cell that had a random number in it and is
'the mean, the last argumend d7 is the standard deviation.

If Cells(8, 4) <> "" Then
Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("d10"), 1, 1, 2, Int(Rnd() * (32767 - 1) + 1), Range("c10").value, _
Range("d7").value
End If

thank you

 

[djpeter]

sorry, here is what i want:

I am currently trying to generate random numbers in a column. I would like to loop the following code so it takes the previous columns number and uses it for the mean.

'first d10 is the output range, the second 1 is for the number of random numbers generated
'the rnd() is for a random seed, the c10 is the past cell that had a random number in it and is
'the mean, the last argument d7 is the standard deviation.

If Cells(8, 4) <> "" Then
Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("d10"), 1, 1, 2, Int(Rnd() * (32767 - 1) + 1), Range("c10").value, Range("d7").value
End If

so i guess the number that has to be looped inside of the code i already have is the range("c10").value to go to ("c11").value

 

[ravishankar]

Hi try

Dim a as integer
If Cells(8, 4) <> "" Then
For a = 10 to 50
Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("d10"), 1, 1, 2, Int(Rnd() * (32767 - 1) + 1), Range("c" & a).value, Range("d7").value
Next a
End If

Ravi

 

[djpeter]

Thanks, that works for looping that, can i use another to loop the d10 part??

 

[ravishankar]

Hi
Try

Dim a as integer, b as integer
If Cells(8, 4) <> "" Then
for b = 10 to 50
For a = 10 to 50
Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("d" & b), 1, 1, 2, Int(Rnd() * (32767 - 1) + 1), Range("c" & a).value, Range("d7").value
Next a
next b
End If

Ravi

 

[djpeter]

unfortunately that does not work, it overwrites the data 40 times such as that for each loop from 10 to 50, do another loop 40 times.

 

[tusharm]

If you mean do you need another loop, the answer is no.

See how the C10 was made dynamic? Do the same for D10.

Thanks, that works for looping that, can i use another to loop the d10 part??

 

[djpeter]

no i don't need another loop but if i do it the way above, it keeps overwriting the data, try it and see. it loops the inner code 40 times, then loops that process 40 times again... in other words, above i am looping the loop.

 

[tusharm]

Thanks for the suggestion but I don't have to try code to see what it does.

Look at my previous post again. Change Ravi's *first* code sample as I indicated.

no i don't need another loop but if i do it the way above, it keeps overwriting the data, try it and see. it loops the inner code 40 times, then loops that process 40 times again... in other words, above i am looping the loop.

 

[djpeter]

it doesn't go to the next cell, it generates 40 random numbers in the same cell then goes to the next, that is not what i want, i want it to go to the next then generate a number and keep doing that. the next number generated is using the c & dynamic for the mean, so as i go down the column, a new number is generated based off the previous columns number.