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.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Leo Elbertse wrote:
I'm absolutely stumped. I've tried many things:

Code as part of my very long VBA code on an xla
Code seperate as part of the workbook
Code with wokbook, worksheet & range fully defined
All kind of changes in my dateformat,
etc.
etc.

It makes no difference, it refuses to work. I think I know why: it
doesn't see the cell as 31-12-2003 but as 37986.

As solution I've found:

Sub Macro1()
For Each c In ActiveSheet.UsedRange
If Left(c.Text, 5) = "31-12" Then c.Formula = "31-12-2004"
Next c
End Sub

But this is, as far as I can see, a lot more work for excel and thus
very much slower. In addition, why does it work for you and not for
me? Are your cells also fomatted as dates or as text?

Leo

On Mon, 22 Dec 2003 14:02:58 -0700, "Jake Marx"
wrote:

Hi Leo,

Where did you put the code? In a standard module? If so, are the
dates to be replaced on the active worksheet? You may want to try
the following statement, which is more explicit:

Sheets("Sheet1").UsedRange.Replace What:="31-12-????", _
Replacement:="31-12-2004", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False