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

[jwb1012]

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?

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)),"")

 

[JoeMo]

Untested and w/o knowing what formulas, if any, are in the nine cells between the ones you reference, see if this works.

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

 

[jwb1012]

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]

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).

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]

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.

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

 

[jwb1012]

You are the man JoeMo! Thank you!!

 

[JoeMo]

You are the man JoeMo! Thank you!!

You are welcome - thanks for the reply.