  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:
``````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
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 Last Comment
John Carney

8/22/2022 - Mon
Dontmilkthis

so can you simply use the 'mod' operator to get the desired irow?

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...
Dontmilkthis

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
SOLUTION
byundt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
Dontmilkthis

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.
John Carney