|
Replace number in formula with ROW number
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! |
Replace number in formula with ROW number
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! |
Replace number in formula with ROW number
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! |
Replace number in formula with ROW number
=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! |
Replace number in formula with ROW number
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! |
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! |
Replace number in formula with ROW number
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 "Sheeloo" wrote: 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! |
| All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com