Bob
You probably don't have the macro placed in the proper module. The
macro I gave you is a sheet event macro. As such it must be placed in the
sheet module for that sheet (the Input sheet). To access the sheet module
for that sheet, first select that sheet. Then right-click on the sheet tab
for that sheet. In the menu that pops up, select View Code. That brings up
the sheet module. Paste the macro into that module. You can click the "X"
in the top right corner of the module to return to the spreadsheet. HTH
Otto
"ZZBC" wrote in message
...
Otto Moehrbach wrote:

Doing this the way that you describe (you have a formula, you want to
change the formula to its value whenever its value changes) is a bit
awkward. I say "awkward" because a Worksheet_Change event macro will not
fire when the value of a formula changes, only when the content of a cell
changes. This leaves the Worksheet_Calculate event macro as the only
macro that will fire. The problem with this is that the
Worksheet_Calculate macro does not have a target cell. The target cell
would be the cell that triggered the calculation. Without a target cell,
the code would have to check the destination cell value against the source
cell value to see if they are the same. The code would also have to check
if the destination cell contained a formula. If both conditions are met,
the code would then change the formula to its value. If your data has
multiple source and destination cells, the code would have to loop through
all of them to find the one that fits both of the above criteria.
A much easier approach is to use a Worksheet_Change event macro in the
Input sheet. That macro would fire whenever the source cell changed.
The code you write in that macro would then simply copy the contents of
the target cell (source cell) to the destination cell in the Output
sheet. A formula would not be needed in the destination cell at any
time. An example of such a macro is:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
With Sheets("Output")
.Range(Target.Address).Value = Target.Value
End With
End Sub

Note that, as written, this macro considers the destination and source
cells to have the same cell addresses.
Please post back if you need more. HTH Otto
"ZZBC" wrote in message
...

Ken Wright wrote:


You can do it via code, but thats it. There is no function/formula that
will convert itself to text automatically.



Can you give me an overview of how to do it in code so I would not take
too many wrong paths ... I may try it?
I've did some coding in Visual Basic ... 2-3 years ago ... I would have
to get my mind back into it.



Well, I created the macro ... doesn't seem to be doing anything ?
I set a toggle point ... did not seem to get there.
Any ideas?

Bob