Find / Replace VBA Code Doesn't Replace Date Problem

[Mav3000]

Hi, I'm Running Excel 2002. I have imported a series of dates from an SQL Database using MS Query.

In a reocrd, users of the Database can set any dates which are 'not applicable' to a record as "N/A". The database records this setting in the date field as the date '31/12/1901 00:00:00' (DD-MM-YYYY HH:MM:SS).

In Excel I format these date columns to be 'DD-MM-YY'.

I am running a simple macro to replace and dates of the format 'DD-MM-YY' with the string "N/A".

My problem is is that while Excel replaces the dates when I do a manual 'find/replace', when I run the recorded macro again with new data no dates are replaced!!

The code that is recorded directly from the macro which worked is:

Cells.Select
Selection.Replace What:="31-12-01", Replacement:="N/A", LookAt:=xlWhole _
, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Buy doesn't work when run!

Any help on this would be greatly appreciated!

I have tried replacing the 'What:=" criteria with the formats "DD-MM-YY", "DD-MM-YYYY", "DD-MM-YYYY HH:MM:SS" to no avail.

Thanks,

Richard

 

[MakeItSo]

Could it be that "31-12-01" refers to december 31 2001 now?
;-)

[blue]Help us, join us, participate
IAHRA - International Alliance of Human Rights Advocates[/blue]

 

[xlbo]

You are also searching for a STRING to replace. You need to replace a date

What:=format("31-12-1901 00:00:00","DD-MM-YY")

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

On a new sheet enter
[tt]
31/12/2007
[/tt]
Notice that it's LEFT justified.

Now enter
[tt]
12/31/2007
[/tt]
Highlight BOTH cells and change Format>Cells - Number tab GENERAL.

What do you see? One is a DATE and one is NOT.

I do queries often from Oracle where I must convert non-date "dates" to REAL DATES. This is what you must do. Wlternatively, you can parse the TEXT that you have into the YEAR, MONTH, DAY protions and CONVERT to a REAL DATE in another column...
[tt]
A B
31/12/1901 00:00:00 =DATE(Mid(A1,7,4),Mid(A1,4,2),mid(A1,1,2))
[/tt]





Skip,

_[red][/red]

 

[Mav3000]

Thanks for your replies.

Converting the date cells to 'general' shows them as Microsoft Date Numbers - 38995, 39035 etc. The dates that need to be replaced with 'N/A' are 731.

What I don't understand is that the macro I recorded replacing these dates records the 'What:=' criteria as a string ("31-12-01").

I have tried to convert this to:

What:=format("31-12-1901 00:00:00","DD-MM-YY")

But this doesn't work either.

Is there a way to format the 'what:=' criteria to replace the numerical data value instead of the DD-MM-YY... versions that don't seem to be working?

 

[xlbo]

ok try this:

What:=format(Datevalue("31-12-1901 00:00:00"),"DD-MM-YY")


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mav3000]

Thanks Geoff, but I'm afraid that hasn't fixed it either. My macro now is:

Cells.Select
Selection.Replace What:=Format(DateValue("31-12-1901 00:00:00"), "DD-MM-YY"), Replacement:="N/A", LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

when I click on one of the dates, it shows as 31/12/1901 in the formula bar.

I don't understand why the recorded macro worked as I recorded it, but not when I run it again...

 

[Mav3000]

GOT IT!

The clause:

What:=DateValue("31/12/1901")

Works!

Thanks very much for the help both Geoff and Skip

Richard

 

[xlbo]

Dates in a FIND are funny beasts

What is the 1st cell with a date in it ? Say it is in B10 then try the following:

What:=Format(DateValue("31-12-1901 00:00:00"), Range("B10").numberformat)

change B10 to be a cell with a date in it

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mav3000]

Thanks Geoff - I've tested my working solution on the three worksheets I'm working on and it works across the board.

I'm not sure why leaving out the 'format' function works, but just using the 'datevalue' function has done the trick.

Thanks for your help,

Rich

 

[xlbo]

no probs - hadn't seen your 10:50 post before I posted back

Glad to see you got it working

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

"...The dates that need to be replaced with 'N/A' are 731."

731 is 12/31/1901.

so if you dates are in column A
[tt]
=IF(A1=731,NA(),A1)
[/tt]



Skip,

_[red][/red]