change increasment in a "for" loop

J

Jared

I have a loop function
For i = 3 To 27
If isempty(i,j) then next i


How do i make i increase by 3?
 
J

JLatham

use the Step option of the For statement:

For i = 3 To 27 Step 3

negative numbers are also allowed as
For i = 27 to 3 Step -3
 
J

Jared

Great,
Thanks

JLatham said:
use the Step option of the For statement:

For i = 3 To 27 Step 3

negative numbers are also allowed as
For i = 27 to 3 Step -3
Click to expand...
 
J

Jared

I do have another question. Is there an option to increase the number by 3
but only on the 3rd fold? running 4 numbers and everytime it reaches a number
which divides into a whole 3 climb by 3.

explenation: 3,4,5,6,9,10,11,12,15,16,17,18,21,22,23 etc..


Thanks, Jared
 
J

JLatham

It's generally considered bad form to mess with loop control values, but it
could be done, cautiously like this. and in this case the very first loop of
j causes nothing to happen with 3, 4 and 5 - because it immediately jumps to
6. Try it and look at the output and you could play with things a bit to see
how to adjust counters and such to get the results you want. This would put
things in rows 7,8,9 13,14,15 19,20,21 and 25,26,27 (in columns B and C).

Sub TestLoopAltering()
Dim i As Integer
Dim j As Integer

For i = 1 To 2
For j = 3 To 27
If (j Mod 3) = 0 Then
j = j + 3
If j > 27 Then
Exit For
End If
End If
Range("A1").Offset(j, i) = "Here I am"
Next
Next

End Sub
 
J

JLatham

I played with it some more after thinking about it some. You really don't
need to increment on MOD 3, you need to increment on MOD 6 in your example.
This code would place entries in the rows you indicated, in 2 columns, swap
i, j around as needed for your row/column offsets:

Sub TestLoopAltering()
'3,4,5,6,9,10,11,12,15,16,17,18,21,22,23
Dim i As Integer
Dim j As Integer

For i = 1 To 2
For j = 1 To 27
Debug.Print j
If (j Mod 6) = 0 Then
j = j + 3
If j >= 27 Then
Exit For
End If
End If
If (j Mod 3) = 0 Then
Range("A1").Offset(j - 1, i) = "Here I am"
Range("A1").Offset(j, i) = "Here I am 2"
Range("A1").Offset(j + 1, i) = "I'm Here 3"
Range("A1").Offset(j + 2, i) = "I'm Here 4"
End If
Next
Next
End Sub
 
Top