|Subject:||Macro: Find and replace|
|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,
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?