changing the counter in a loop

zviito

New Member
Joined
Apr 17, 2002
Messages
6
hello I have written a little program below and it works as intended. How ever, I am completely stuck on the next step.

Option Explicit

Sub Example()
Dim Ramdom As Single
Dim i As Integer
Dim j As Integer
For j = 1 To 10
For i = 1 To 7
Ramdom = Rnd()
' generates a random number
If Ramdom < 0.5 Then _
Cells(i, j).Value = 0 _
' if the random number is < 0.5 a "0" results
Else: Cells(i, j).Value = 1
' if the random number is > 0.5 a "1" results
Next i
Next j
End Sub

The program (try it if you want) produces a 7 x 10 table of numbers (0's and 1's).

I would however like to put in an element whereby the 0 represents fail and 1 a pass.
If there is a 0 I would like the next two numbers to be 0 (regardless of what the random number generator gives).

I have tried changing the value of the counters (i & j) but always get bad results
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi zviito,

Here's what I came up with, I added some font coloring as a way to test the results. This can easily be removed.

Sub Example2()
Dim i, j, rand, zCounter, zFlag, color As Integer
Range("a1:j7").Font.ColorIndex = color
zFlag = 1
For j = 1 To 10
For i = 1 To 7
rand = Application.WorksheetFunction.Round(Rnd(), 0) * zFlag
Cells(i, j).Value = rand
Cells(i, j).Font.ColorIndex = color
If rand = 0 And zCounter < 2 Then
zCounter = zCounter + 1
zFlag = 0
color = 3
Else
zCounter = 0
zFlag = 1
color = 0
End If
Next i
Next j
End Sub

hope that helps,
giacomo
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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