VBA - Change certain cell references in formula to absolute references

[Ride The Lightning]

Hi all

I want to change certain cell references in formula to absolute references. I already have the following code that changes all the cell references in a range to absolute references:

For Each cell In Range("AUM14:AWT14")
    If cell.HasFormula = True Then
            cell.Formula = Application.ConvertFormula _
                (Formula:=cell.Formula, _
            fromReferenceStyle:=xlA1, _
        toReferenceStyle:=xlA1, toAbsolute:=xlAbsolute)
    End If
Next

How would amend the code to only change certain cell references to absolute references? For example, change only "D3" in formula below to absolute:

=AUM7&AUM6&":"&AUM7&D3

will become:

=AUM7&AUM6&":"&AUM7&$D$3

Any help will be appreciated

 

[Ride The Lightning]

.. but I can't see how you could add anything to Find/Replace to avoid such replacements. That is unless you insert the whole formula - in which case you might as well just edit it in the cell? Did you have something else in mind?


@RTL
My suggestion would be to return to vba and try the following. If any part of the particular cell reference is already absolute, my code is supposed to leave that reference as it was.
That is, $D$3, $D3 and D$3 should all remain as that if found in a formula.
If you want those "absoluted" to $D$3 as well, post back.

Sub FixFrmla()
  Dim cell As Range
  
  Const RefToFix As String = "D3"
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^A-Z\$])(" & RefToFix & ")(\D|$)"
    For Each cell In Range("AUM14:AWT14")
      If cell.HasFormula Then
        If .Test(cell.Formula) Then
          cell.Formula = .Replace(cell.Formula, "$1" & Replace(Range(RefToFix).Address, "$", "$$") & "$3")
        End If
      End If
    Next cell
  End With
End Sub

Hi Peter, thank you very much for that, that works great!

For future use, if I want to simply use another single cell reference (for example, A4 instead of D3) would I just change the following lines?:

Sub FixFrmla()
  Dim cell As Range
  
  Const RefToFix As String = "A4"
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^A-Z\$])(" & RefToFix & ")(\A|$)"
    For Each cell In Range("AUM14:AWT14")
      If cell.HasFormula Then
        If .Test(cell.Formula) Then
          cell.Formula = .Replace(cell.Formula, "$1" & Replace(Range(RefToFix).Address, "$", "$$") & "$4")
        End If
      End If
    Next cell
  End With
End Sub

With regards to the other comments regarding the nature of the formulas in the range. they are inconsistent formulas for the most part. some have D3.

I was not aware of those expressions to use in VBA, awesome to learn new tools

Thanks again (and also to FDibbins )

 

[Peter_SSs]

Hi Peter, thank you very much for that, that works great!

For future use, if I want to simply use another single cell reference (for example, A4 instead of D3) would I just change the following lines?:

No, simpler than that.
The only line to change in my previous code would be:

Const RefToFix As String = "A4"

 

[Ride The Lightning]

Bringing this thread back up as I would like to adjust the code provided by Peter (it replaces a cell value within a formula to a cell value with absolutes):

Sub FixFrmla()
  Dim cell As Range
  
  Const RefToFix As String = "D3"
  
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "([^A-Z\$])(" & RefToFix & ")(\D|$)"
    For Each cell In Range("AUM14:AWT14")
      If cell.HasFormula Then
        If .Test(cell.Formula) Then
          cell.Formula = .Replace(cell.Formula, "$1" & Replace(Range(RefToFix).Address, "$", "$$") & "$3")
        End If
      End If
    Next cell
  End With
End Sub

I would like the code to be adjusted to one that can also:

1. check a formula and replace with Column absolute
2. check a formula and replace with Row absolute

I have been trying to find a solution but with no success, if anyone can point me in the correct direction, it would be much appreciated.

 

[Peter_SSs]

1. check a formula and replace with Column absolute.

Would you be looking, for example, to change
- just any reference to cell D3 to $D3 or
- any reference to column D at all to $D (eg D3 to $D3 and D34 to $D34 etc) or
- all column references to $column references (eg D3 to $D3, AB7 to $AB7, G:G to $G:$G etc)?

 

[Ride The Lightning]

Would you be looking, for example, to change
- just any reference to cell D3 to $D3 or
- any reference to column D at all to $D (eg D3 to $D3 and D34 to $D34 etc) or
- all column references to $column references (eg D3 to $D3, AB7 to $AB7, G:G to $G:$G etc)?

Hi Peter, just the 1st option, "just any reference to cell D3 to $D3"

 

[Peter_SSs]

Try this version. The input box allows you to choose any of the 3 options: row absolute only, column absolute only, both absolute.
The input box will accept upper or lower case letters and the order of entering "RC" wouldn't matter.

Sub FixFrmla_v2()
  Dim cell As Range
  Dim RX As Object
  Dim Resp As String, sAdr As String
  
  Const RangeToCheck As String = "G3:G10"   '<- Change to suit
  Const RefToFix As String = "D3"           '<- Change to suit
  
  Resp = UCase(Application.InputBox(Prompt:="Enter" & vbLf & "R for row only" & vbLf & "C for column only" & vbLf & "RC for row and column", _
                              Title:="Change to absolute address"))
  If Resp = "R" Or Resp = "C" Or Resp = "RC" Or Resp = "CR" Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "([^A-Z\$])(" & RefToFix & ")(\D|$)"
    Select Case Resp
      Case "RC", "CR"
        sAdr = Range(RefToFix).Address(1, 1)
      Case "R"
        sAdr = Range(RefToFix).Address(1, 0)
      Case "C"
        sAdr = Range(RefToFix).Address(0, 1)
    End Select
    
    For Each cell In Range(RangeToCheck)
      If cell.HasFormula Then
        If RX.Test(cell.Formula) Then
          cell.Formula = RX.Replace(cell.Formula, "$1" & Replace(sAdr, "$", "$$") & "$3")
        End If
      End If
    Next cell
  Else
    MsgBox "Not a valid choice. No replacements made."
  End If
End Sub

 

[Ride The Lightning]

Try this version. The input box allows you to choose any of the 3 options: row absolute only, column absolute only, both absolute.
The input box will accept upper or lower case letters and the order of entering "RC" wouldn't matter.

Sub FixFrmla_v2()
  Dim cell As Range
  Dim RX As Object
  Dim Resp As String, sAdr As String
  
  Const RangeToCheck As String = "G3:G10"   '<- Change to suit
  Const RefToFix As String = "D3"           '<- Change to suit
  
  Resp = UCase(Application.InputBox(Prompt:="Enter" & vbLf & "R for row only" & vbLf & "C for column only" & vbLf & "RC for row and column", _
                              Title:="Change to absolute address"))
  If Resp = "R" Or Resp = "C" Or Resp = "RC" Or Resp = "CR" Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.Pattern = "([^A-Z\$])(" & RefToFix & ")(\D|$)"
    Select Case Resp
      Case "RC", "CR"
        sAdr = Range(RefToFix).Address(1, 1)
      Case "R"
        sAdr = Range(RefToFix).Address(1, 0)
      Case "C"
        sAdr = Range(RefToFix).Address(0, 1)
    End Select
    
    For Each cell In Range(RangeToCheck)
      If cell.HasFormula Then
        If RX.Test(cell.Formula) Then
          cell.Formula = RX.Replace(cell.Formula, "$1" & Replace(sAdr, "$", "$$") & "$3")
        End If
      End If
    Next cell
  Else
    MsgBox "Not a valid choice. No replacements made."
  End If
End Sub

Superb, works perfectly, thank you.

Can I just ask you to explain the following line in the code please?

(cell.Formula, "$1" & Replace(sAdr, "$", "$$") & "$3")

Is it to do woth R1C1 notation or something?

Much obliged.

 

[Peter_SSs]

Superb, works perfectly, thank you.

Good news.

Can I just ask you to explain the following line in the code please?

(cell.Formula, "$1" & Replace(sAdr, "$", "$$") & "$3")

Is it to do woth R1C1 notation or something?

No, it is nothing to do with R1C1. What you quoted from my code is only part of a line that involves 'Regular Expressions'.
For me regular expressions are hard to do & often harder to explain.

The gist of it is that in RegExps, the $ sign has a special meaning and since a cell reference may also contain a $ sign (eg B$3), I needed to double up any $ signs in the address before applying the RegExp operation. So if I want to replace "B3" with "B$3", I have to fist make it "B$$3" so that the RegExp operation only removes one of the $ signs.

It's vaguely like directly in a worksheet, if you want to use the 'Find' dialog to find a ~ character, you have to put ~~ in the Find box.

If you are not familiar with RegExps, one introduction is here: Regular expressions - An introduction

 

[Ride The Lightning]

Good news.


No, it is nothing to do with R1C1. What you quoted from my code is only part of a line that involves 'Regular Expressions'.
For me regular expressions are hard to do & often harder to explain.

The gist of it is that in RegExps, the $ sign has a special meaning and since a cell reference may also contain a $ sign (eg B$3), I needed to double up any $ signs in the address before applying the RegExp operation. So if I want to replace "B3" with "B$3", I have to fist make it "B$$3" so that the RegExp operation only removes one of the $ signs.

It's vaguely like directly in a worksheet, if you want to use the 'Find' dialog to find a ~ character, you have to put ~~ in the Find box.

If you are not familiar with RegExps, one introduction is here: Regular expressions - An introduction

Nice one, thanks for the explanation and the link. All the best.