Thanks:  0
Likes:  0

# Thread: Fixing cells (\$) for multiple cells

1. ## Fixing cells (\$) for multiple cells

I know I can put a \$ sign for all references in a formula for a cell by pressing F4, but is there a quick way for doing this for multiple cells (e.g. a column) or do I have to go through all the cells individually?

e.g.
=A1
=A2
=A3

to

=\$A\$1
=\$A\$2
=\$A\$3

Thanks

3. ## Re: Fixing cells (\$) for multiple cells

Thanks VoG

It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

Cheers

4. ## Re: Fixing cells (\$) for multiple cells

You can use Edit-> Replace to a limited extent.

Just select the range of cells, do something like Replace "A" with "\$A\$".

5. ## Re: Fixing cells (\$) for multiple cells

Originally Posted by sputnik17
Thanks VoG

It seems like you have to buy that (for a proper legal version for a company anyway). I suppose I could just write a macro for a large amount of cells.

Cheers
yes you can, look at the convertformula function...

Here's a simple example

Range("A1").Formula = Application.ConvertFormula(Range("A1").Formula, xlA1, xlA1, 1)

The 4th argument is the absolute style

1 = col & row abs
2 = row abs, col relative
3 = col relative, row abs
4 = row & col relative

Unfortunately, it converts the ENTIRE formula's references. so you can't mix it up like

=\$A\$1+B1

you couldn't have it convert to that, both cell refs would have the same absolute style..