|
Tricky OFFSET routine
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 |
Tricky OFFSET routine
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" wrote in message ... 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 |
Tricky OFFSET routine
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 "Bernie Deitrick" wrote: 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" wrote in message ... 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 |
Tricky OFFSET routine
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" wrote in message news:981282DD-5615-42A7-AE00- ... 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. |
Tricky OFFSET routine
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 "Bernie Deitrick" wrote: 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" wrote in message news:981282DD-5615-42A7-AE00- ... 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. |
| All times are GMT +1. The time now is 09:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com