troubleshooting Question

# Excel VBA that resets a variable every 29 rows asked on
Microsoft Excel
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
Dontmilkthis
###### Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.