(Please note that I've worked around the issue below by changing every cell
manually, so this isn't an urgent request - but I imangine I may need a
better answer in future, and it's possible other people might also be
grateful if anyone has a solution.)

I have a block of cells containing a variety of unrelated formulae. I want
to change one element of each formula from a relative to an absolute
reference.

I tried highlighting all the cells, clicking in the formula bar and pressing
f4 (three times, to make column reference absolute and leave the row
reference relative) but this changes only the first cell in the block.

If the formulae were in sequence I could change one and copy it to the other
cells, but since they are all different that won't work. Does anyone know how
to change the references throughout a selecteed group of cells?

Hi Giles,

Gord Dibben answered something similar on the 30th Oct 2008, if you go to
page 211 and look for the thread titled "Convert selected formula references
from relative to absolute" it should answer your question.
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"Giles" wrote:

(Please note that I've worked around the issue below by changing every cell
manually, so this isn't an urgent request - but I imangine I may need a
better answer in future, and it's possible other people might also be
grateful if anyone has a solution.)

I have a block of cells containing a variety of unrelated formulae. I want
to change one element of each formula from a relative to an absolute
reference.

I tried highlighting all the cells, clicking in the formula bar and pressing
f4 (three times, to make column reference absolute and leave the row
reference relative) but this changes only the first cell in the block.

If the formulae were in sequence I could change one and copy it to the other
cells, but since they are all different that won't work. Does anyone know how
to change the references throughout a selecteed group of cells?

Hi
Try the following macro :

Sub test()
Dim c As Range
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlA1)
End If
Next c
End Sub

HTH
Daniel

Hi Giles,

Gord Dibben answered something similar on the 30th Oct 2008, if you go to
page 211 and look for the thread titled "Convert selected formula references
from relative to absolute" it should answer your question.
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"Giles" wrote:

(Please note that I've worked around the issue below by changing every cell
manually, so this isn't an urgent request - but I imangine I may need a
better answer in future, and it's possible other people might also be
grateful if anyone has a solution.)

I have a block of cells containing a variety of unrelated formulae. I want
to change one element of each formula from a relative to an absolute
reference.

I tried highlighting all the cells, clicking in the formula bar and pressing
f4 (three times, to make column reference absolute and leave the row
reference relative) but this changes only the first cell in the block.

If the formulae were in sequence I could change one and copy it to the other
cells, but since they are all different that won't work. Does anyone know
how to change the references throughout a selecteed group of cells?

Warren, Daniel,

Thanks, both, for your replies - just what I needed (and astonishingly fast,
too).

Giles.

"Daniel.C" wrote:

Hi
Try the following macro :

Sub test()
Dim c As Range
For Each c In Selection
If c.HasFormula = True Then
c.Formula = Application.ConvertFormula(c.Formula, xlA1, , xlA1)
End If
Next c
End Sub

HTH
Daniel

Hi Giles,

Gord Dibben answered something similar on the 30th Oct 2008, if you go to
page 211 and look for the thread titled "Convert selected formula references
from relative to absolute" it should answer your question.
--
Regards
Warren
Excel Novice
Addiewell, Scotland.

If this helps please click the Yes button.


"Giles" wrote:

(Please note that I've worked around the issue below by changing every cell
manually, so this isn't an urgent request - but I imangine I may need a
better answer in future, and it's possible other people might also be
grateful if anyone has a solution.)

I have a block of cells containing a variety of unrelated formulae. I want
to change one element of each formula from a relative to an absolute
reference.

I tried highlighting all the cells, clicking in the formula bar and pressing
f4 (three times, to make column reference absolute and leave the row
reference relative) but this changes only the first cell in the block.

If the formulae were in sequence I could change one and copy it to the other
cells, but since they are all different that won't work. Does anyone know
how to change the references throughout a selecteed group of cells?