Help with cell reference

**rlkerr** · 11-03-2006, 01:22 PM

I know that if you f2 on a cell and f4 it will change the formula cell reference to what I call hard code them.

ex. $a$1

I need to know how to write a vba statement to change all whole range.

example

Q5:Q48 has formulas like this =SUM(S18:AD18).
I need them all to be hard coded; like this =SUM($S$18:$AD$18).

Is there a way to write a vba code for this.

Thanks
rlkerr

**Mallycat** · 11-03-2006, 08:05 PM

This will do it

Sub ConvertAbsolute()
    Dim InputFormula As String
    Dim newFormula As String
    InputFormula = Range("A1").Formula
    newFormula = Application.ConvertFormula(Formula:=InputFormula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, toabsolute:=xlAbsolute)
    Range("A1").Formula = newFormula
End Sub

**davesexcel** · 11-03-2006, 08:09 PM

Originally Posted by rlkerr

I know that if you f2 on a cell and f4 it will change the formula cell reference to what I call hard code them.

ex. $a$1

I need to know how to write a vba statement to change all whole range.

example

Q5:Q48 has formulas like this =SUM(S18:AD18).
I need them all to be hard coded; like this =SUM($S$18:$AD$18).

Is there a way to write a vba code for this.

Thanks
rlkerr

You could write the formula like that in the first place.............

Select the entire range and enter the formula with the absolutes then hit Ctrl enter that should work

**rlkerr** · 11-06-2006, 02:08 PM

Mallycat
The code is giving me an error on the line newformula.

I just opened my sheet, highlighted the range and ran the macro.

Did I do something wrong?

**Carim** · 11-06-2006, 02:14 PM

Hi,

Just type your formula in cell A1 ...
and then run your macro ...

HTH
Carim

**MDubbelboer** · 11-06-2006, 03:21 PM

Originally Posted by Carim

Hi,

Just type your formula in cell A1 ...
and then run your macro ...

HTH
Carim

or alter the code to reflect the range where your formulas are kept

very good code mallycat, i had looked at this post earlier and ran away screaming, as I had no idea.

made a very small modification and added it to my personal.xls as

Sub ConvertAbsolute()
    Dim InputFormula As String
    Dim newFormula As String
    Dim ActRange As Range
    
    Set ActRange = Selection
    
    For Each Cell In ActRange
        InputFormula = Cell.Formula
        newFormula = Application.ConvertFormula(Formula:=InputFormula, fromReferenceStyle:=xlA1, toReferenceStyle:=xlA1, toabsolute:=xlAbsolute)
        Cell.Formula = newFormula
    Next Cell
End Sub

this just allows you to highlight the range you want converted, then run macro to change to absolute.