Marc
To change relative to absolute manually.
1. Type in the $ signs
2. F2 and highlight the address(es) in the cell and hit F4 to cycle through
choices.
OR you can run a macro to change all at once.
Here are 4 such macros......
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
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Run the macro by going to ToolMacroMacros.
Gord Dibben Excel MVP
On Sun, 24 Apr 2005 16:44:28 GMT, "Marc"
wrote:
David solved the problem and it was in the original and absolute values.
The cells that didn't paste correctly didn't have the $ making them absolute
values. How do I make sure that they have the $ when I C&P?
Marc
"David McRitchie" wrote in message
...
Hi Marc,
Repeating:
Please post a short example of the address range you
copied from, and the formulas by address.
It is impossible to guess what your problem is without a
description of the problem:
what you did, what formula you copied, from where to where,
what formula go got, and what you expected.
You have to show how the formula changed and what you expected.
Even if we tried to unravel your long paragraph, there are no
formulas -- you must provide a reproducible example of your
problem.
Example: -----------
A1: 2 B1: 4 C1=8 D1=ROW() E1: =SUM(A1:D1) displays 15
copy A1:E1 paste into A2, resulted in
A2: 2 B2: 4 C2=8 D2=ROW() E1: =SUM(A2:D2) displays 16
What I expected was: (describe what you wanted/expected)
-------------------
Are you familiar with relative and absolute addresses.
Relative And Absolute Addressing
... formula containing a relative address, Excel will adjust the row
and/or column
references ... This type of cell reference is called absolute addressing.
...
http://www.cpearson.com/excel/relative.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Marc" wrote..
I've tried it several ways. I'm creating a work book to generate
quotes. I
enter everything on the first page and it fills the good, better, best