Help with cell reference

[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

[Mallycat]

This will do it

Please Login or Register  to view this content.

[davesexcel]

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]

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]

Hi,

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

HTH
Carim

[MDubbelboer]

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

Please Login or Register  to view this content.

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

[rlkerr]

That is great. It works exactly how I wanted it too. Thanks to everyone for helping me out. That saved me about 3 or 4 hours work.