Replace text within formula - with "dynamic" absolute reference to cell 1 row above

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Okay.. this is kind of a tongue twister but... I need to replace the text "$D$26" within column D with an absolute reference to the cell address of *the cell 1 row above* the cell containing this formula. The formula will appear every 10 rows. I have the code below, that seems to work but... I cant figure out how to do a this "dynamic" absolute reference.

Is this possible?


Code:
Sub FindAndReplace()
    Dim Findtext As String
    Dim Replacetext As String
    Findtext = "$D$26:"
    Replacetext = " [I][U][B]< insert "dynamic" absolute reference to the cell 1 row above >[/B][/U][/I] "
    Columns("D").Replace what:=Findtext, replacement:=Replacetext, lookat:=xlPart, MatchCase:=FalseEnd Sub

For example,

Cell D27 BEFORE Code:
=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"")

Cell D37 BEFORE Code:
=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L36='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D36, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"")

---

Cell D27 AFTER Code:
=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"")

Cell D37 AFTER Code:
=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$36:$D36, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"")
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested and w/o knowing what formulas, if any, are in the nine cells between the ones you reference, see if this works.
Code:
Sub ReplaceRef()
Dim c As Range, Adr As String
Adr = "$D$26"
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each c In Range("D:D").SpecialCells(xlCellTypeFormulas)
       If InStr(c.Formula, Adr) > 0 Then
              c.Formula = Replace(c.Formula, Adr, c.Offset(-1, 0).Address(1, 1))
       End If
Next c
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
End Sub
 
Last edited:

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
It worked!! The only issue is that it is an array formula, so I need to indicate that somehow. Any thoughts?

You are amazing!!! Thank you so much!
 

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
I am hoping to slightly modify the wonderful code you provided me to make it run on all sheets that begin with "Labor BOE" but it doesn't work on all sheets. I am thinking it's something with the code I added. Any thoughts?

Also, I still need to signal that these are array formulas within the code (from last comment).


Code:
Sub ReplaceD26()
Dim c As Range, Adr As String
Adr = "$D$26"
Application.Calculation = xlCalculationManual
On Error Resume Next
[U][B][I]For Each sh In ActiveWorkbook.Sheets
    If Left(sh.name, 9) = "Labor BOE" Then[/I][/B][/U]
    
For Each c In Range("D:D").SpecialCells(xlCellTypeFormulas)
       If InStr(c.FORMULA, Adr) > 0 Then
              c.FORMULA = Replace(c.FORMULA, Adr, c.Offset(-1, 0).Address(1, 1))
       End If
Next c
On Error GoTo 0
[U][I][B]End If
Next sh[/B][/I][/U]
Application.Calculation = xlCalculationAutomatic
End Sub
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,461
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

I am hoping to slightly modify the wonderful code you provided me to make it run on all sheets that begin with "Labor BOE" but it doesn't work on all sheets. I am thinking it's something with the code I added. Any thoughts?

Also, I still need to signal that these are array formulas within the code (from last comment).
Again this is untested.
Code:
Sub ReplaceRef()
Dim c As Range, Adr As String, Sh As Worksheet
Adr = "$D$26"
Application.Calculation = xlCalculationManual
On Error Resume Next
For Each Sh In Worksheets
    If Sh.Name Like "Labor BOE*" Then
        For Each c In Sh.Range("D:D").SpecialCells(xlCellTypeFormulas)
               If InStr(c.FormulaArray, Adr) > 0 Then
                      c.FormulaArray = Replace(c.FormulaArray, Adr, c.Offset(-1, 0).Address(1, 1))
               End If
        Next c
        On Error GoTo 0
    End If
Next Sh
Application.Calculation = xlCalculationAutomatic
End Sub
 

Forum statistics

Threads
1,136,275
Messages
5,674,782
Members
419,524
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top