I work frequently with spreadsheets that are created through an export
from a particular application.These sheets have dates in them, that
use a Customformat d"-"m"-"yyy. Although the cells are recognised as
dates, I'm more than happy to change this to a standard date format
should that be necessary.

The problem is, the outside application works with dates, that based
on when they were first created default to:

31-12-2000
31-12-2001
31-12-2002
31-12-2003

For the purpose of my job, I have to change all of these to:

31-12-2004

When I do this without VBA it works perfectly. Recording a macro gives
the following result

Cells.Replace What:="31-12-????", Replacement:="31-12-2004", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Entering this code in my VBA project, or even simply running this one
line of code does absolutely nothing however.

Anyone has an idea how to achieve this?

Leo Elbertse

Why not
for each c in selection
c.value=left(c,len(c)-4)&2004
next

--
Don Guillett
SalesAid Software

"Leo Elbertse" wrote in message
...
I work frequently with spreadsheets that are created through an export
from a particular application.These sheets have dates in them, that
use a Customformat d"-"m"-"yyy. Although the cells are recognised as
dates, I'm more than happy to change this to a standard date format
should that be necessary.

The problem is, the outside application works with dates, that based
on when they were first created default to:

31-12-2000
31-12-2001
31-12-2002
31-12-2003

For the purpose of my job, I have to change all of these to:

31-12-2004

When I do this without VBA it works perfectly. Recording a macro gives
the following result

Cells.Replace What:="31-12-????", Replacement:="31-12-2004", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Entering this code in my VBA project, or even simply running this one
line of code does absolutely nothing however.

Anyone has an idea how to achieve this?

Leo Elbertse

This might work if the only dates were 31-12-????, however there are
all kind if dates and only the 31-12 variant that I need to change,

thanks,
leo


On Mon, 22 Dec 2003 10:45:48 -0600, "Don Guillett"
wrote:

Why not
for each c in selection
c.value=left(c,len(c)-4)&2004
next

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

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

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


Leo Elbertse wrote:
I work frequently with spreadsheets that are created through an export
from a particular application.These sheets have dates in them, that
use a Customformat d"-"m"-"yyy. Although the cells are recognised as
dates, I'm more than happy to change this to a standard date format
should that be necessary.

The problem is, the outside application works with dates, that based
on when they were first created default to:

31-12-2000
31-12-2001
31-12-2002
31-12-2003

For the purpose of my job, I have to change all of these to:

31-12-2004

When I do this without VBA it works perfectly. Recording a macro gives
the following result

Cells.Replace What:="31-12-????", Replacement:="31-12-2004", _
LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

Entering this code in my VBA project, or even simply running this one
line of code does absolutely nothing however.

Anyone has an idea how to achieve this?

Leo Elbertse

I just recorded your original code and it worked fine.
Sub Macro1()
Range("B1:B4").Select
Selection.Replace What:="31-12-????", Replacement:="31-12-2004",
LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Don Guillett
SalesAid Software

"Leo Elbertse" wrote in message
...
This might work if the only dates were 31-12-????, however there are
all kind if dates and only the 31-12 variant that I need to change,

thanks,
leo


On Mon, 22 Dec 2003 10:45:48 -0600, "Don Guillett"
wrote:

Why not
for each c in selection
c.value=left(c,len(c)-4)&2004
next

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

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

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.