troubleshooting Question

Excel VBA that resets a variable every 29 rows

Avatar of John Carney
John CarneyFlag for United States of America asked on
Microsoft Excel
6 Comments2 Solutions230 ViewsLast Modified:
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
    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.

I hope i haven't made this too confusing.


Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
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.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros