Macro: Find and replace

Giganews Newsgroups
Subject: Macro: Find and replace
Posted by:  Bertie
Date: Mon, 29 May 2006

I have recorded a macro (see below) to find a particular date amongst other
dates in a certain column (nb the date to be found has more than one entry in
this column).  The macro is recorded ok however when I go back to play it
(nb. I do return the data back to its original date ie. 5/01/2006), it cannot
find the date although it found it the first time round when it was recorded
(the date is definitely there). The data in the column is formatted as:
[$-C09]ddd, d mmm yyyy which translates to Thu, 5 Jan 2006 on the screen.
The cell data is a direct input of 5/01/2006. Can anyone explain why the
macro cant find the data again?

    Selection.Replace What:="5/01/2006", Replacement:="3/01/2006", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _

I also find it strange that the replace function in excel will only look-in
"formulas" not "values", however if I did a search only using the find
function it will provide a look-in "values" option in addition to the
"formulas" option.  Using the data set above I am doing a different search on
"Thu, 5 Jan 2006" it will find it using look-in "values" but not using the
look-in "formula" option. This doesnt help me because I need to find and
replace the data and if the "values" option isnt included with the replace
function I cant do it.  Does anyone have any suggestions to get around it?