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:

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

I had a further thought on this issue and realised a slight problem with the code, in that you'll get 0,1,2,3 sequences, which I imagine aren't wanted based on your explanation.

I put your code in a sub so I could check Compilation.

To avoid needing the auxiliary column, I used iRow as a counter. It is initialized to 3, then an IIf statement either adds 1 or resets it to 4. Though this is one more statement than the Mod approach suggested by Dontmilkthis, you may find the logic easier to understand.

Sub Test()Dim cel As Range, lft As Range, rgt As RangeDim iRow As Long, iCol As LongDim lCel As String, rCel As StringiRow = 3For Each cel In Range([F4], [D10000].End(xlUp).Offset(0, 2)) iRow = IIf(iRow = 32, 4, iRow + 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 celEnd Sub

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

John CarneyReliability Business Tools Analyst IIAuthor Commented:

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.

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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