VBA search and replace not working

[jaspa]

Hi all , and a Merry Christmas!

I have been trying to figure out why a piece of vba is not working.
I'm searching through a colum of data that has dates and some cells contain W/C and a date e.g W/C 26/12/17.
I can get the macro to strip the W/C and the space between the text and date however my problem is:
If I do this manually (recording as a macro) it will remove the W/C and space and convert the cell into a date which is exactly what I need, but If I run this as a macro it leaves the cell format as general. The code I'm using is:

Columns("F:F").Select
Selection.Replace What:="W/C ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Any help much appreciated.

Jaspa

 

[Fluff]

How about

   With Columns("F")
      .Replace What:="W/C ", Replacement:="", LookAt:=xlPart, _
         SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
         ReplaceFormat:=False
      .NumberFormat = "dd/mm/yy"
   End With

 

[Scott T]

When I run your code it is converted to a date.