Replace number in formula with ROW number
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" wrote:
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
"Jacob Skaria" wrote:
=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
--------------
Jacob Skaria
"MikeF" wrote:
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" wrote:
Please paste the formula
If this post helps click Yes
--------------
Jacob Skaria
"MikeF" wrote:
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!
|