Changing Multiple Cells from Relative to Absolute Functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a spreadsheet full of formulas in columns, and I am trying to copy them all to a different spreadsheet in rows. I can copy the formulas over easy enough, but when I try to transpose them, because there are relative references to cells it totally messes up all the formulas. How do I change all the references in these cells from relative to absolute without going into every single cell and hitting F4 which would take an eternity? Or at least how do I block Excel from changing the cell references when I paste without having to individually change each reference to absolute

Dave
 
Dave

I could post a macro for changing cell references, but might be easier to use
this code copied from someone in these News Groups. Proper attribution
escapes me. Apologies to whomever.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTo.Offset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount

UserCancelled:
End Sub

Gord Dibben Excel MVP
 
Hi Dave,

One way would be to Find and Replace all "=" with "|" (or some other
unharmful character), copy and paste and re-replace. If Excel doesn't
recognize the cell contents as a formula, it will not adjust anything.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

chendaddy said:
I have a spreadsheet full of formulas in columns, and I am trying to copy
Click to expand...
them all to a different spreadsheet in rows. I can copy the formulas over
easy enough, but when I try to transpose them, because there are relative
references to cells it totally messes up all the formulas. How do I change
all the references in these cells from relative to absolute without going
into every single cell and hitting F4 which would take an eternity? Or at
least how do I block Excel from changing the cell references when I paste
without having to individually change each reference to absolute?
 
Back
Top