asked on # Excel VBA that resets a variable every 29 rows

I have a code that loops through Range([F4], [D10000].End(xlUp).Offset(0, 2)), and looks for a the value of the variable iRow. Right now, iRow is determined by a helper column (Column G) which cycles through the numbers 4 to 32. The code finds the value for iRow in Column G:

Here's my current code:

I hope i haven't made this too confusing.

Thanks,

John

```
iRow = cel.Offset(0, 1)
```

But I would like to determine iRow with code that resets itself to 4 every 29 rows without the need for the helper column. When the code is looking at F4, iRow = 4, for F5, iRow = 5, etc. And the series repeats itself starting with F33, F62, F91 etc.Here's my current code:

```
For Each cel In Range([F4], [D10000].End(xlUp).Offset(0, 2))
iRow = cel.Offset(0, 1)
iCol = [AllWeeks].Find(what:=cel.Offset(0, -1), LookIn:=xlValues).Column - 1 'Cells(4, iCol).Select 'Cells(iRow, 100).End(xlToLeft).Offset(0, -1).Select
Set lft = Cells(iRow, iCol)
Set rgt = lft.Offset(0, 1)
If rgt = "" Then
Set rgt = rgt.End(xlToLeft)
Set lft = rgt.Offset(0, -1)
End If 'Range(lft, rgt).Select
lCel = lft.Address(0, 0)
rCel = rgt.Address(0, 0)
cel.Formula = "=IFERROR(AVERAGE(" & lCel & ":" & rCel & ")/30.5*7,"""")"
If cel.Offset(0, 2) = "RFS" Then cel = ""
Next cel
```

All I need is something that goes back to iRow = 4 when the macro gets to F33, and again at F62, and F91 etc.I hope i haven't made this too confusing.

Thanks,

John

Microsoft Excel

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

I'd be interested in seeing which is more efficient from a CPU cycle point of view...

might be worth actually testing that aspect if they both work as expected.

performing two additions and a mod operation might be a bit more expensive than the if else statement and an addition.

might be worth actually testing that aspect if they both work as expected.

performing two additions and a mod operation might be a bit more expensive than the if else statement and an addition.

Thank you both! Brad, I actually came up with something last night based on iRow +1, but the Double "I" If statement is new to me and more elegant than my If-Then statement.

Dontmilkthis, I didn't know how to use the mod statement and I love the fact that it's a one-liner.

Again, thank you both.

~ John

Dontmilkthis, I didn't know how to use the mod statement and I love the fact that it's a one-liner.

Again, thank you both.

~ John

They should have an"A+ Sublime" category.

irow = cel.Row mod 29

at F4:

cel.Row = 4

4 mod 29 = 4

irow = 4

at F33

cel.Row = 33

33 mod 29 = 4

irow = 4

at F34

cel.Row = 34

34 mod 29 = 5

irow = 5

and so on...