Thanks Jake,

I´ll keep this solution in mind for the future. In this case I don´t
really need it since, in thruth, I use:

LastRow = ActiveSheet.UsedRange.Rows.Count
For Each c In Range("e2:e" + Trim(LastRow))

and column e only has hard coded dates in it.

Unfortunately I can't use your suggestion regarding ScreenUpdating.
These few lines of code are part of a very very lengthy project and
stangely enough:

Turning off screenupdate results in a terrible mess at the end of the
project: Suddenly my maximized sheet no longer is maximized. I
actually see parts of several sheets on my screen but without the
requisite scrollbars around it.

Mind you, that is not the only problem: Saving a workbook from within
the project has a less than 50% chance of succeeding. It sometimes
works but other times (with no changes to the code) it doesn't.

Actually I'm using these holidays to see whether I can streamline the
code (thus this date issue) in the hope that it will work more
consistently. Still, allowing for the frequent manual save and
code-restart, the code has been successful for the last 18 months.

Regards,

Leo

On Tue, 23 Dec 2003 08:48:17 -0700, "Jake Marx"
wrote:

Hi Leo,

Ah, that's it. <g I didn't think of what was happening here. I'm in the
US, where our dates are typically in the format mm-dd-yyyy. 31-12-2001
would not be considered a date on my system, so Excel treated it as a text
string. On your system, it is interpreted as a date, so Excel stores it in
the cell as a numeric value. Hence, the find/replace won't work on your
system, but it will work on mine (replacing text). The Text property
returns what is displayed in the cell, so your new code works fine (but
slowly, I'm sure).

I don't know if there's a good solution (other than the one you're currently
using). You may be able to speed it up a bit, but probably not a whole lot.
You could try this routine to see if it's any quicker:

Sub ConvertDates()
Dim rng As Range

For Each rng In Sheet1.UsedRange. _
SpecialCells(xlCellTypeConstants)
If IsDate(rng.Value) Then
If Day(rng.Value) = 31 And _
Month(rng.Value) = 12 Then
rng.Value = DateSerial(2004, 12, 31)
End If
End If
Next rng
End Sub

This assumes that your dates are hardcoded (not formulas). If not, then
you'll have to look at the entire UsedRange instead of just the Constants
within the UsedRange. Turning off ScreenUpdating and setting Calculation to
manual at the beginning (and resetting them at the end) may help speed
things up a bit, too.