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