Replace number in formula with ROW number

[MikeF]

My routine "lands" on cell m1000, and there is a row formula in it that
contains cell m$6.

Using find-and-replace, would like to change the 6 to row 998.
To be specific, it should be absolute two rows above wherever the routine
"lands" my active cell.

Examples:

Lands on cell 200, $6 should change to $198.
Lands on cell 10020, $6 should change to $10018.
Lands on cell 1744, $6 should change to $1742.
**** Lands on cell 1746, $6 should change to $1744.
**** Lands on cell 808, $6 should change to $806.
.... And so on.

Any assistance will be greatly appreciated.
Regards,
- Mike

ps: Bernie MVP, if you're reading this, it could be the solution. Thanx!

 

[Jacob Skaria]

Please paste the formula

If this post helps click Yes

 

[MikeF]

Ok, here it is....

=SUMIFS($Z8:$AS8,INDIRECT("$Z"&ROW(OFFSET(M8,ROW($M$6)-ROW(M8),0))&":"&"$AS"&ROW(OFFSET(M8,ROW(M$6)-ROW(M8),0))),"k")

There are 2 instances of ROW(m$6).
The "$6" needs to change to 2 rows above whatever cell ROW(m8) becomes, as
per the examples below.

Thanx,
- Mike

 

[Jacob Skaria]

=SUMIFS($Z8:$AS8,INDIRECT("$Z"&ROW(OFFSET(M8,ROW($M6)-ROW(M8),0))&":"&"$AS"&ROW(OFFSET(M8,ROW(M6)-ROW(M8),0))),"k")

Copy the formula to the same cell from where you copied the below..

If this post helps click Yes

 

[MikeF]

Jacob, thank you, but this will not work.

There is *much* more going on.

the $6 needs to be *absolutely* 2 rows above the *first* cell in multiple
groups of 202 rows.
So the formula in row 9 *still* needs to refer to row(m$6), as do the
formulas in rows 10 thru 210.
Then the formula in row 213 needs to refer to row(m$211), as do the formulas
in rows 214 thru 415.
And so on ....

That's why it seems that a replace $6 with *whatever* row is 2 rows above
the first row in the group seems to be the answer.

It's a TRICKY one!!

Thanx again for any assistance you can provide.
- Mike

 

[Sheeloo]

Hello Mike,

Try the macro below.. You can adapt it to your need...

Sub rowMinusTwo()
Dim currentRow As Variant
With ActiveCell
currentRow = Split(.Address, "$")
currentRow(2) = currentRow(2) - 2
.Formula = WorksheetFunction.Substitute(.Formula, "$6", "$" & currentRow(2))
MsgBox ActiveCell.Formula
End With
End Sub

 

[MikeF]

Sheeloo ... woo-HOO!
I *think* you've nailed it.
Works in a few test examples, let me incorporate it into the real deal and
will let you know.
THANK you!!
- Mike