On Fri, 9 Jul 2004 20:38:49 +1000, "macropod" wrote:

Don Wiss wrote:
Some of our users are getting data from clients and pasting the data into
the spreadsheet. Sometimes the years are text. Even though the cell is
formatted as a number, being text it has a value of 0 to any cell that
references it. If I use a macro to test its value it gives me a number. If
I can find a way to detect this situation I can issue a
Range("YearColumn").Value = Range("YearColumn").Value
when deactivating the sheet. Or maybe just always do this when
deactivating?

Visually, if the column is formatted as general, 'text' dates will be left
aligned, while 'numeric' dates will be right aligned.

But we have formatted the column as centered.

Making sure all dates
are numeric, is as simple as multiplying them by 1. This will convert the
'text' dates to 'numeric' date equivalents, though you may lose the cell's
date formatting in the process if you do it in the cell.

This will not work. As the dates are text they have a numeric value of 0.
Multiplying anything against them leaves their value at 0.

What I have done is every time the sheet is deactivated I simply run this
line: Range("YearColumn").Value = Range("YearColumn").Value

It works fine. While there are some on screen numbers that don't appear
while still on the sheet, all critical numbers are on later sheets, and
running this line makes them all correct. And when they return, the
calculated numbers on the input sheet will then appear.

Don <donwiss at panix.com.