Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace within workbook macro | Excel Discussion (Misc queries) | |||
Find & Replace in VB macro | Excel Discussion (Misc queries) | |||
Creating a macro to find and replace text | Excel Worksheet Functions | |||
macro to Find Replace in Excel | Excel Discussion (Misc queries) | |||
VB Find and Replace | Excel Worksheet Functions |