VBA to make part of current selection absolute

[norts55]

Please help. This seems simple but I have exhausted everything I can think of.

Is there a way after my selection is made to make part of a formula absolute?
Below is a simple formula that I have in a cell. This formula can be referring to any cells, not just the cells I am referring to.
I have a macro that is using some search criteria to select a cell with this formula, once the cell is selected I need the F675 to be $F$675 (absolute). But remember this can be any cell reference. I hope this makes sense. If not, please let me know and I can try to explain better.

Is now "=(I681*F675)"
Needs to be "=(I681*$F$675)"

 

[norts55]

Everyone, thank you very much for the help. Joe4, this works great and is doing exactly what I am looking for. However, I have one more hurdle. These are old formulas that someone else wrote. They put parentheses around the formula. I am finding if there is parentheses, the code isn't working. So, is there a way to either remove the parentheses or make this code work if there is parentheses? See below of how these formulas look.

=(S681*F675)

 

[skillilea]

If you are trying to set a cell try this

Public Function MakeAbsolute(inRng As Range) As String
    Dim form As Variant
    form = Split(inRng.Formula, "*")
    MakeAbsolute = form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1)
End Function


Public Sub TestIt()
    Range("A1").Formula = MakeAbsolute(Range("C1"))
End Sub

 

[skillilea]

Public Function MakeAbsolute(inRng As Range) As String
    Dim form As Variant
    form = Split(inRng.Formula, "*")
    MakeAbsolute = Replace(Replace(form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1), ")", ""), "(", "")
    
End Function


Public Sub TestIt()
    Range("A1").Formula = MakeAbsolute(Range("C1"))
End Sub

 

[norts55]

I am going to mark this as solved. If needed, I will start a new thread for removing the parentheses. Thank you everyone. As always, this group is great.

 

[skillilea]

this line removes the parentheses

 MakeAbsolute = Replace(Replace(form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1), ")", ""), "(", "")

 

[norts55]

For some reason that is not working for me. I have copied that entire code, ran "ab_TestIt" but get a Run-Time error here.

 

[skillilea]

try this

Public Function MakeAbsolute(inRng As Range) As String
    Dim form As Variant

    form = Split(inRng.Formula, "*")
    MakeAbsolute = Replace(form(0), "(", "") & "*" & Application.ConvertFormula(Replace(form(1), ")", ""), xlA1, xlA1, 1)
End Function

 

[Joe4]

For some reason that is not working for me. I have copied that entire code, ran "ab_TestIt" but get a Run-Time error here.


View attachment 106081

I am guessing that you did not update the ranges used in the Sub. It is looking at the formula in cell C1.
Wasn't your formula in cell C2 in your example?

 

[skillilea]

Try the code in post #17. It did have a bug with the replace, should be fixed now

 

[norts55]

I still get a debug here.