Tricky OFFSET routine

[MikeF]

In the following code:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _

"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))),""k"")"

.... Need the first row the offset formula is looking for, in this case 1220,
to *always* be -2, or in other works 2 rows above what 0 is.
Which in this case would indeed be 1220, but if r[0] is actually row 8 the
offset would look to row 6 to subtract row 8.

So in short, r1220 needs to be r-2 .... however that can be formulated.

Have tried many syntaxes that result in the routine stopping, but am sure
there is an easy answer.
If anyone can help, it would be sincerely appreciated.

Thanx,
- Mike

 

[Bernie Deitrick]

Mike:

Either subtract two from your final row count:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))),""k"")"

Or just move your row reference up by 2 rows:
Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))),""k"")"

HTH,
Bernie
MS Excel MVP

 

[MikeF]

Thank you for the reply.
I erred in my description.
It's not that easy.

The offset is always 2 rows above the first row.
.... 3 rows above the second row.
.... 4 rows above the third row.
In groups of 200 rows, then starts again, but we can disregard that part for
now [!!].

So the first row, if hard-entered in the relevant place, would be ...

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))),""k"")"

The second ...

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-3,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-3,0))),""k"")"

The third ...

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-4,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-4,0))),""k"")"

.... And so on.
I had tried something like rows(m$1:m1018)-row(m1220), but it always returns
-2 as it goes down the rows, not -2, -3, -4, and so on.

Guess I could hard-enter the whole 200 rows like this, but was hoping for a
more elegant and efficient solution.

Thanx,
- Mike

Mike:

Either subtract two from your final row count:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C)-2,0))),""k"")"

Or just move your row reference up by 2 rows:
Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _
"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1218C13)-ROW(R[0]C),0))),""k"")"

HTH,
Bernie
MS Excel MVP

In the following code:

Cells(65536, 13).End(xlUp).End(xlUp).FormulaR1C1 = _

"=SUMIFS(R[0]C26:R[0]C45,INDIRECT(""$Z""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))&"":""&""$AS""&ROW(OFFSET(R[0]C,ROW(R1220C13)-ROW(R[0]C),0))),""k"")"

... Need the first row the offset formula is looking for, in this case
1220,
to *always* be -2, or in other works 2 rows above what 0 is.
Which in this case would indeed be 1220, but if r[0] is actually row 8 the
offset would look to row 6 to subtract row 8.

So in short, r1220 needs to be r-2 .... however that can be formulated.

Have tried many syntaxes that result in the routine stopping, but am sure
there is an easy answer.
If anyone can help, it would be sincerely appreciated.

Thanx,
- Mike

 

[Bernie Deitrick]

Mike,

Try changing

rows(m$1:m1018)-row(m1220)

to

rows(m$1:m$1018)-row(m1220)

which should increment as it is copied down the column.

HTH,
Bernie
MS Excel MVP

 

[MikeF]

Thanx Bernie.
We're getting there, now I need to add in the final issue ...
First off, one more error on my part ...
rows(m$1:m$1018)-row(m1220)
Should be
rows(m$1:m$1218)-row(m1220)
That way the offset is looking for criteria 2 rows above the *first* row.

But as earlier presented, this is in groups of 200, or actually 202
including the two criteria rows above each group.

The starting group's "first row" is row 8, which would be...
rows(m$1:m$6)-row(m8)

The second group's first row would be in row 210, so ...
rows(m$1:m$208)-row(m210)

The third group's first row would be in row 412, so ...
rows(m$1:m$410)-row(m412)
.... And so on.

Which brings me back to the original [restated slightly] "offset always
needs to be specifically and "absolutely" 2 rows above whatever the *first*
row is [only].

In thinking this through, perhaps I could have the code find-and-replace the
original
rows(m$1:M$6)-row(m8)

with rows(m$1: REPLACE "M$6" WITH "ROW 0-2" )-row(m****)
???

Thanx again for working thru this with me, it presents a tricky set of
parameters.
Regards,
- Mike