Supress col/row increment with copy

[scrodchunk]

Question with (hopefully) an easy answer....

When you copy a cell that uses a formula referring to another cell,
for simple illustration say "=C4" it will increment based on where
you paste it. So, it will increment up to "=C5" if you paste it in
the next cell, or "=D4" if you paste below.

How can I suppress that, so that when I paste the new cell receives
"=C4" as well.

(I know I can simply copy the text and paste that in, but I want to
copy a whole row of formulas to be the same. I thought
Paste>Special>Formulas would work, but it seems to be the same as
regular paste)

Thnx in advance

Ross.

 

[Peo Sjoblom]

Use absolute references

=$C$4

you can select the cell in question, press F2 and then press F4
and it will change. Or you can run a macro like this

Sub RepCellAbs()

Dim formula As String
Dim cell As Object

For Each cell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)

formula = cell.formula
cell.formula = Application.ConvertFormula(formula, _
fromreferencestyle:=xlA1, _
toreferencestyle:=xlA1, toabsolute:=xlAbsolute)

Next

End Sub

 

[scrodchunk]

Thanks for the replies about absolute references - I should have
mentioned that the column I'm copying is one that needs to be created
with copies as well, so hvaing absolute references there will impede
me from copying and creating the source cells in the first place.

Using F4 to change each cell in the column to absolute before copying
is one approach I guess - but still labour intensive.

Ideally I'd like to set up my references in one cell, copy and paste
to create appropriate formulas in the next (with increments happening)
then copy that whole column and <ALT><SHIFT><LIFT LEFT LEG> Paste to
get that column to make my new column have the same formulas (without
increments). Sounds like a macro is the only way to go.

Thx.
Ross.

 

[Gord Dibben]

Ross

Create your first column using relative addressing so's you can increment.

Then select that column and run this macro to change to absolute.

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

Now you can copy this column to another.

OR just use the following to copy the formulas as is to the other column.

Cannot remember who first posted this code. Maybe Tom Ogilvy or John
Walkenbach but.......???

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 XL2002