How to make universal changes to a whole range of cells and formulas?

  • Thread starter Thread starter hungn
  • Start date Start date
H

hungn

I can't seem to figure out how to make global changes to a whol
worksheet of formulas.

Is it possible to make changes to all cells at once? For example, i
it possible to make all references in cells absolute references at th
same time?

Also, I would like to copy a range of cells on one worksheet, "master"
(with formulas that refer to another worksheet, worksheet1, for example
to paste into another section of the same "master" worksheet, bu
keeping the original formulas referring to the same cells on
different worksheet, such as worksheet2.

I would much appreciate any help anyone can offer. And thanks i
advance!

Please let me know also if you need further clarification to help m
with this issue.

Hun
 
Hung

You would need VBA to make global changes to cell references.

Here are four........

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)
End If
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)
End If
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)
End If
Next
End Sub

One method of moving formulas without changing references is.....

Select all formulas.

Edit>Replace

What: =
With: xxx

Replace all.

Copy the formulas which are now text to new area then reverse the
edit>replace.

To change references from sheet1 to sheet2 you could also use the
edit>replace.

Gord Dibben Excel MVP
 
Back
Top