asked on

Code that will repeat a fixed sequence of values every 13 rows

I have some code that relies on the value sequence 4 ...16 every 13 rows starting in the fourth row. Right now I'm accomplishing this “reset” of the iRow values with a helper range in Column G. (G17, G30, and G43 all equal 4: G18, G31, and G44 all equal 5, etc, etc.)

Dim iCol As Long, iRow As Long, lCel As String, rCel As String
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 
    lCel = Cells(iRow, iCol).Address
    rCel = lft.Offset(0, 1).Address
    Cells(iRow, 6).Formula = "=IFERROR(AVERAGE(" & lCel & ":" & rCel & ")/30.5*7,"""")"
Next cel

Open in new window

However, I would like to accomplish this “reset” of the iRow values without the helper range in Column G.

How do I do that?

Thanks,
John

ASKER CERTIFIED SOLUTION

Ken Butters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

I get a value of -1 when I use that line of code. Could you please re-post with that line in its proper context within my original code?

Thanks,
John

Ken Butters

Can you post a sample workbook... That might make a difference too.

John Carney

ASKER

hi buttersk, I've gotten overwhelmed with more pressing problems at work so I'm going to assume that your suggestion will work and close the question. Sorry for the wild goose chase!

Thanks,
John