Macro: Find and replace

[Bertie]

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?

Macro:
Columns("D:D").Select
Selection.Replace What:="5/01/2006", Replacement:="3/01/2006", LookAt:= _
xlWhole, SearchOrder:=xlByRows, MatchCase:=True,
SearchFormat:=False, _
ReplaceFormat:=True

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?


--
B

[Dave Peterson]

Sometimes, you can do:

Columns("D:D").Replace What:=dateserial(2006,1,5), _
Replacement:=dateserial(2006,1,3), _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=True

And excel/vba will behave better.

Bertie wrote:
>
> 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?
>
> Macro:
> Columns("D:D").Select
> Selection.Replace What:="5/01/2006", Replacement:="3/01/2006", LookAt:= _
> xlWhole, SearchOrder:=xlByRows, MatchCase:=True,
> SearchFormat:=False, _
> ReplaceFormat:=True
>
> 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?
>
> --
> B

--

Dave Peterson