Changing relative to absolute cell references in large block of cells

[Confused]

I have a large block of cells set up that I now find I need to move. The
cells were originally set up as relative since I needed to do things like
fill down & right, etc. But now I find I need to move that entire block of
cells to another location, and of course, now the cells are referencing to
cells that don't exist. I need to change the entire block before moving and
I don't see any way to do that for a large set of cells.

Thanks again for any help
Conf.

 

[AltaEgo]

If planned properly, both relative and absolute reference normally move
without problem. Possibly you are making the whole cell reference absolute
rather than just a row or column (eg using $A$1 when you only need $A1). The
follow reference may help you understand how to make absolute reference to a
row or column only.

http://www.techonthenet.com/excel/questions/referencing.php

If this doesn't help, ask your question again giving examples of the formula
giving you trouble, how and where you are moving the formula and a
description of what happens when you move it.

HTH

 

[Gord Dibben]

Select the block of cells and run the Absolute macro.

I threw the others in just in case you need any other combination.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub

Sub AbsoluteRow()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsRowRelColumn)
Next
End Sub

Sub AbsoluteCol()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelRowAbsColumn)
Next
End Sub

Sub Relative()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlRelative)
Next
End Sub


Gord Dibben MS Excel MVP

 

[Confused]

That's just the problem, I didn't plan properly :-( actually, I thought I
had everything where it was supposed to be, but found later when I was
writing my lookups that I had to move entire blocks of cells from where I
thought they needed to be to where the actually needed to be for the lookup
to work. They are simple "copy to" formula that just take the scattered
information from another file and organize it into a useful grid in the new
file so that lookups can be done on them:

='[Pricing.xls]Sheet1'!AX3
='[Pricing.xls]Sheet1'!AX4
='[Pricing.xls]Sheet1'!AX5

etc

Of course, I just placed the first cell, with the AX3 reference, then did a
fill down to make the rest to a certain point, skipped a few cells from the
original file, then made a new reference and so on. The problem occurred
when I tried my lookups and found that I needed some of those blocks of
cells to be next to the last column of the first block, and tried to move
them, and the references to the first file changed to
='[Pricing.xls]Sheet1'!EX3

Just a misunderstanding of how the lookup worked and bad planning on my
part.

Conf

 

[Confused]

Excellent, I thought it might need a macro, that will come in handy, thanks
a bunch.

Conf.

 

[AltaEgo]

You've done nothing we haven't all done at some time or other ... learned a
valuable lesson that sometimes hastier is slower!