copy exact formulas

  • Thread starter Thread starter Guillermo
  • Start date Start date
G

Guillermo

Is there a way to copy formulas EXACTLY as they appear, without the cell
references moving around?
I mean, if I have the following formula in cell A1:
=5+A5+A6

I want to copy it to the cell C1, but I want it to be the exact formula
=5+A5+A6, not to change the reference to the C column

I know a way to do it is to go in the cell, select the formulas "text" and
then paste it, but that doesnt work in case of multiple cells..

guillermo
 
Guillermo said:
Is there a way to copy formulas EXACTLY as they appear, without the cell
references moving around?
I mean, if I have the following formula in cell A1:
=5+A5+A6

I want to copy it to the cell C1, but I want it to be the exact formula
=5+A5+A6, not to change the reference to the C column

I know a way to do it is to go in the cell, select the formulas "text" and
then paste it, but that doesnt work in case of multiple cells..

guillermo
Click to expand...


Hi,

depending on what you intend to do, try the relative or abolute Address
in your formula in A1, say:

= 5 + $A5 + $A6
or
= 5 + $A$5 + $A$6

regards,

Jean
 
Guillermo

Change the formulas to ABSOLUTE.

=5+$A$5+$A$6

To avoid typing the $ signs, select the cell references in the formula bar and
hit F4 key to cycle through the options.

To change many at one time you would use a macro. If you want that, post
back.

Gord Dibben Excel MVP
 
thanks.
I was trying to avoid the absolute references, just because my case are not
as simple as presented it in my first message.
Instead of one formula, I have a few cells, for which I need NOT to have an
absolute reference (because I also may drag them around and need them to
change)
The only thing I need now, is to have an exact copy of that group of cells
somewhere else in the spreadsheet. If I move the table, it works fine, but
what I want is to have the same effect as moving the table but leaving an
exact copy in the initial place.
Copying from the formula bar works, but only for an individual cell; if I
need multiple cells I have to do it one by one.

thanks,


guillermo
 
Select your range.
Edit|replace
what: = (equal sign)
with: $$$$$= (some unique string--not used anywhere else!)

copy|paste

Then fix up both ranges:
edit|replace
what: $$$$$= (whatever you used)
with: = (equal sign)
 
awesome.
I was looking for an Excel function that does it directly, but this trick
around it works like a charm.

thanks!

guillermo
 
Hi Guillermo,
see Right Click Menus (Context Menus) in Excel
http://www.mvps.org/dmcritchie/excel/rightclick.htm

Copy Formula and Paste Formula in context menu
based on postings by Chip Pearson
Only setup to copy from one cell and paste to one cell
without changing the formula when pasted.

So if you want to do a range of cells and possibly even
paste a range with Transpose you'd want to stick with
the method demonstrated by Dave Peterson.
 
Guillermo

To copy a range of cells without changing references......

Code from somewhere, so cannot attribute to originator.

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

awesome.
I was looking for an Excel function that does it directly, but this trick
around it works like a charm.

thanks!

guillermo
Click to expand...
 
thanks,

guillermo

Gord Dibben said:
Guillermo

To copy a range of cells without changing references......

Code from somewhere, so cannot attribute to originator.

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

Click to expand...
 
Back
Top