how do I paste without EXCEL increasing the cell number ?

C

cgs

How do I cut and paste formulas and resultents with out EXCEL increasing the
cell number ?
 
R

Ron Coderre

I seems like you need to set parts of your references to be Absolute, instead
of Relative.

In a formula, a dollar sign ($) locks in that part of the reference.

Examples:
A1: =$C3

When copied and pasted elsewhere in the worksheet, that formula will ALWAYS
reference Col_C, but the referenced row will change.

A1: =$C$3
will always reference cell C3 no matter where it is pasted.

For more information, search Excel help for "range references" and read the
section on:
"The difference between relative and absolute references"

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
J

joeu2004

cgs said:
How do I cut and paste formulas and resultents with
out EXCEL increasing the cell number ?
Click to expand...

Although changing relative references to absolute references
might be the best solution for you, there are times when
you might want to do exactly as you stated, retaining the
relative references for some other purpose, e.g. so that they
are indeed modified appropriately when you subsequently
copy-and-paste.

Since you say __cut__ and paste, not copy, a cell move
might meet your needs. Highlight the cells to "cut", then
move the cursor to the highlight border so that you see a
four-way-arrow cursor, then drag the cells to where you
want them. Excel modifies relative references to cells
within the moved cell block, but other relative references
are unmodified.

In cases where I actually want to __copy__ and paste, I
have resorted to a text copy-and-paste. This is unpleasant
because it must be done one cell at a time. Highlight one
cell and copy the text of the formula in the "fx" field.
Press Esc to deselect the cell, then highlight the new
location, then do paste.
 
H

Harlan Grove

[email protected] wrote...
....
In cases where I actually want to __copy__ and paste, I
have resorted to a text copy-and-paste. This is unpleasant
because it must be done one cell at a time. Highlight one
cell and copy the text of the formula in the "fx" field.
Press Esc to deselect the cell, then highlight the new
location, then do paste.
Click to expand...

If the formulas to be copied exactly were in a single area range,
easier to select that range, Edit > Replace = with |=, copy then paste
elsewhere, then select both copied and pasted ranges and Edit > Replace
|= with =. If that still too unpleasant, use a macro.


Sub foo()
Dim r As Range, ac As Variant

If Not TypeOf Selection Is Range Then Exit Sub

On Error Resume Next

Set r = Application.InputBox( _
Prompt:="Select paste range", _
Title:="Formula Copy", _
Type:=8 _
)

If Err.Number <> 0 Or r Is Nothing Then
Err.Clear
Exit Sub
ElseIf r.Cells.Count <> Selection.Cells.Count Then
Set r = r.Cells(1).Resize(Selection.Rows.Count,
Selection.Columns.Count)
End If

On Error GoTo CleanUp

ac = Application.Calculation
Application.Calculation = xlCalculationManual

Selection.Replace What:="=", Replacement:="|=", lookat:=xlPart
Selection.Copy Destination:=r
Union(Selection, r).Replace What:="|=", Replacement:="=",
lookat:=xlPart

CleanUp:
Application.Calculation = ac

End Sub
 
Top