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

 

[FDibbins]

have you considered using Find/Replace for this, instead of VBA?

FIND D3 REPLACE $D$3

the more you add to that, the more accurate it becomes....
FIND =AUM7&AUM6&":"&AUM7&D3 REPLACE =AUM7&AUM6&":"&AUM7&$D$3

 

[Ride The Lightning]

have you considered using Find/Replace for this, instead of VBA?

FIND D3 REPLACE $D$3

the more you add to that, the more accurate it becomes....
FIND =AUM7&AUM6&":"&AUM7&D3 REPLACE =AUM7&AUM6&":"&AUM7&$D$3

Hi, I must admit I did not! Was too wrapped up in thinking of a VBA solution, Doh!

Cheers

 

[FDibbins]

Happy to help

 

[Peter_SSs]

have you considered using Find/Replace for this, instead of VBA?

FIND D3 REPLACE $D$3

You'd need to be very careful with that.

Examples

Excel Workbook
	A	B	C
1	Valid Formula	Replacement Result	Notes
2	=3*$D3	=3*$$D3	Invalid formula
3	=AD3&"x"	=A$D$3&"x"	Invalid formula
4	=D3&D33	=$D$3&$D$33	Valid formula, but has "absoluted" BOTH references
Sheet1

 

[FDibbins]

Pete, VERY true, that's why I added my footnote...

the more you add to that, the more accurate it becomes....
FIND =AUM7&AUM6&":"&AUM7&D3 REPLACE =AUM7&AUM6&":"&AUM7&$D$3

 

[Peter_SSs]

Pete, VERY true, that's why I added my footnote...

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

 

[FDibbins]

Pete, I guess it just depends on how much you intend to change. On a rethink, if all that is changing is the $D$3, then maybe just change that in the 1st formula and copy it down

 

[Peter_SSs]

Pete, I guess it just depends on how much you intend to change.

True, and what you have to start with. We don't know what variety of formulas exist in the range in question nor how many refer to D3 nor in what way they do.

Never-the-less it was a general question for which we have both provided some input and the OP can decide if any of the suggestions are what was wanted.

 

[FDibbins]

Always good to get different inputs, we all )often) read things from a different perspective
(just noticed - its coming up for the weekend, time for you guys to get hit with the football vid spam )