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

 

[skillilea]

Here I have it working with this

Book1
	A	B	C
1	0		0
2
3
4
296135 - GSO

Cell Formulas
Range	Formula
A1	A1	=G1*$E$1
C1	C1	=(G1*E1)

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


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

 

[Joe4]

I still get a debug here.


View attachment 106087

What exactly is in cell A1?

 

[norts55]

A1 is empty.

 

[Joe4]

A1 is empty.

Sorry, I meant what is in cell C1.

 

[norts55]

C1 is empty also.

 

[Joe4]

C1 is empty also.

That is why you are getting the error, because it you are running the exact code he posted, it is trying to apply that to cell C1, as indicated here:

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

You need to change the "C1" in that formula to the cell you want to run this against (where your current formula resides).
And you need to change the "A1" to the cell that you want the result to go into. If you want to to update the current formula, both these cells should be the same value.

 

[norts55]

Sorry for my lack of understanding. Coding is way beyond my skill set and I maybe making this more frustrating and confusing than it really is. But what I am thinking is, I need this to run against in my active (currently picked) cell as I will be picking cells anywhere on my sheet that have this formula. The dynamic formula you gave me in post #10 works great, just not with parenthesis. Is there a way to make that formula work with parenthesis?

 

[Joe4]

Sorry for my lack of understanding. Coding is way beyond my skill set and I maybe making this more frustrating and confusing than it really is. But what I am thinking is, I need this to run against in my active (currently picked) cell as I will be picking cells anywhere on my sheet that have this formula. The dynamic formula you gave me in post #10 works great, just not with parenthesis. Is there a way to make that formula work with parenthesis?

Note that the parentheses really serve no purpose in that formula and really do not need to be there.

But try this version, which will handle either case (with or without parentheses).

Public Sub UpdateFormula()
    Dim form As Variant
    form = Split(ActiveCell.Formula, "*")
    If Right(form(1), 1) = ")" Then
        ActiveCell.Formula = form(0) & "*" & Application.ConvertFormula(Left(form(1), Len(form(1)) - 1), xlA1, xlA1, 1) & ")"
    Else
        ActiveCell.Formula = form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1)
    End If
End Sub

 

[norts55]

That works perfectly. Thank you!

I agree, the parentheses don't need to be there. These are old formulas, that I did not create and copied hundreds of times.

You solved my issue. Thank you very much.

 

[Joe4]

You are welcome.
Glad we were able to help!