Macro: Find and replace
 

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

Macro: Find and replace
 

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