I have a block of cells with formulas. I want to change all of the cells
within the block to include $ in the formulas so that I can copy the block
and have each block identical.

http://www.asap-utilities.com/downlo...-utilities.php
ASAP UTILITIES has a great add in for this under "formulas"( free download )
But you can also go to Find- Replace

e.g. =(F11-F6)/(F14*3)

Find : F
Replace with : $F$ etc


"clischak" wrote:

I have a block of cells with formulas. I want to change all of the cells
within the block to include $ in the formulas so that I can copy the block
and have each block identical.

To do exactly what you're asking would either require VBA or be manually done.

But....this may be what you "need":

Select the range of formulas

From the Excel main menu:
<edit<replace
Find what: =
Replace with: |= (that's the pipe character on the [\] key above [enter])
Click [replace all]
(That will prepend a pipe char to all of the formulas, making them text )
Copy the range to wherever you want

Select the range(s) impacted
<edit<replace
Find what: |=
Replace with: =
Click [replace all]
(that will convert them back to the original formulas)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"clischak" wrote:

I have a block of cells with formulas. I want to change all of the cells
within the block to include $ in the formulas so that I can copy the block
and have each block identical.

Another Way... Press Ctrl+` (ToolsOptionsViewFormulas) and then copy
formulas to notepad and back to new destination.

Or... on large ranges (2K) Press Ctrl+` and then Ctrl+C twice and
paste back using the icon on the task pane.

clischak wrote:

I have a block of cells with formulas. I want to change all of the cells
within the block to include $ in the formulas so that I can copy the block
and have each block identical.

To change all at once would require a macro,

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

Might be just as easy to select the the cells and EditReplace

What: =

With: ^^^

Replace all.

Copy the cells to new area then reverse the editreplace in both ranges.


Gord Dibben MS Excel MVP

On Thu, 14 Dec 2006 08:19:00 -0800, clischak
wrote:

I have a block of cells with formulas. I want to change all of the cells
within the block to include $ in the formulas so that I can copy the block
and have each block identical.