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

 

[Micron]

Don't know what you mean by selection (could be cell that you've selected or text that you've selected) but maybe you can make use of the .Address property. That value is returned like $F$675. So if you've selected a cell and are using the .Selection event, that could be .Selection.Address. So perhaps

sheets("001").range("F5").formula = "=F10*" & Selection.Address

or to use a hard coded cell reference
sheets("001").range("F5").formula = "=F10*" & range("F5").Address

 

[norts55]

Thank you for the response. I am going to keep trying what you gave for a suggestion but I am not sure that is what I am looking for.

Hopefully I can better explain here.

I manually pick a cell, and within this cell there is a formula such as =I681*F675. I want that formula to be =I681*$F$675, because I am going to copy and paste this to another cell. I need the "I681" part of this formula relative so it will update when I paste to a new cell but I need the "F675" absolute so it stays locked to that location. The problem I am having is the formula is not always =I681*F687, it could be any cell reference such as =I204*F199. In this case I need I204 to stay relative and F199 to be absolute. =I204*$F$199

Again thank you for the response. Hope this explanation helps.

 

[skillilea]

For clarity: 1681 and 1204 are values. Can't you just modify the formula to be absolute? Hit F4 when highlighted.

example: =I681*$F$675 is in A1

After if I copy the cell and put it in B2: Its the same, correct?

Where are the formulas being generated, are they in a macro?

 

[norts55]

Thank you for the response. Sorry for the confusion.
Hopefully this clears things up. Doing a web search I found this code (below). This is doing the conversion from relative to absolute on the entire formula, but I only need the conversion on the second part/cell of the formula. i.e. S681*F675 needs to be S681*$F$675. Is there a way to do that?

ActiveCell.Formula = Application.ConvertFormula(ActiveCell.Formula, xlA1, xlA1, 1)

 

[skillilea]

Are you always multiplying ie. is there always a * in the formula?

 

[norts55]

Yes.

 

[skillilea]

this work

Public Sub t()
    Dim form As Variant
    form = Split(Range("C2").Formula, "*") '  =C1 * F1
    Debug.Print form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1)
End Sub

 

[norts55]

Thanks for the response. I appreciate the help but I am not sure why this isn't working for me. If I put information in the cells called out in your code and run the macro, nothing happens. The other concern I have is, I need this to work on any cell reference not just Cell C2 with the formula C1*F1.

 

[Joe4]

Thanks for the response. I appreciate the help but I am not sure why this isn't working for me. If I put information in the cells called out in your code and run the macro, nothing happens. The other concern I have is, I need this to work on any cell reference not just Cell C2 with the formula C1*F1.

View attachment 106062

That is because it is just spitting out the value and not writing it back to the cell. Just update the code like this:

Public Sub t()
    Dim form As Variant
    form = Split(Range("C2").Formula, "*") '  =C1 * F1
    Range("C2").Formula = form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1)
End Sub

BTW, if you want to make it more dynamic, this version will work on any active cell at the time it is run:

Public Sub t()
    Dim form As Variant
    form = Split(ActiveCell.Formula, "*") 
    ActiveCell.Formula = form(0) & "*" & Application.ConvertFormula(form(1), xlA1, xlA1, 1)
End Sub