Unable to maintain formatting on find/replace

**samwise78** · 10-20-2012, 05:16 PM

Hi there,

New to the forum so be gentle!

Working on a sheet to automatically replace specific text in a search string with either today's date or yesterday's date in UK format (dd/mm/yyyy)
dd/mm/yyyy to be replaced with today's date
xx/xx/xxxx to be replaced with yesterday's date

My really basic code works to an extent but flips the formatting into US date format every time it is run..

Attached what I'm working on - am a novice when it comes to this - think I'm missing something obvious..

Any help would be gratefully appreciated

thanks
Sam

**mike7952** · 10-20-2012, 05:30 PM

Try this use Format(Range,"dd/mm/yyyy")

Replacement:=Format(Range("C3"), "dd/mm/yyyy")

**samwise78** · 10-20-2012, 05:38 PM

Absolutely spot on - works perfectly..
Was staring me in the face the whole time!

Thanks
Sam

**mike7952** · 10-20-2012, 05:50 PM

You can do away with Select and copy to.

    Range("C3").Select
Selection.Copy

and just use

Cells.Replace What:="dd/mm/yyyy", Replacement:=Format(Range("C3"), "dd/mm/yyyy"), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

Cells.Replace What:="xx/xx/xxxx", Replacement:=Format(Range("C5"), "dd/mm/yyyy"), LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False