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...
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
Title | # Comments | Views | Activity |
---|---|---|---|
Excel Formula - If And on Blank Cell | 7 | 24 | |
Excel file merge 2 cells with line break | 2 | 22 | |
Excel: How would I make a depleting dropdown list that shows only values that havent been chosen before? | 4 | 48 | |
Dynamic Vlookup Function Formula Help | 4 | 13 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
10 Experts available now in Live!