Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
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.
iRow = cel.Offset(0, 1)
All I need is something that goes back to iRow = 4 when the macro gets to F33, and again at F62, and 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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.