Be seen. Boost your questionâ€™s priority for more expert views and faster solutions
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.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.Sub Test()
Dim cel As Range, lft As Range, rgt As Range
Dim iRow As Long, iCol As Long
Dim lCel As String, rCel As String
iRow = 3
For 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 cel
End Sub
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
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...