Date re-formatting (using find & replace) works manually but not via VBA

[GillyB]

Hi All,

I have searched high and low for a solution to this problem.

The system I use churns out Excel spreadsheets with the date format 00.00.0000 however, to get Excel to recognize this, especially when I want to sort and filter then I need to change the format to 00/00/0000.

When I do this manually I highlight the columns with dates and do a simple find and replace, replacing '.' with '/', it works perfectly, and all the dates are correct.

However when I run my Macro, which includes the code below, a select number of dates are changed to the US format, which messes up all of my worksheet calculations.

Workbooks("Save Here.xls").Worksheets("Save Here").Range("H:I").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Workbooks("Save Here.xls").Worksheets("Save Here").Range("G:G").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

Has anyone seen this before, or something like it? Any help would be much appreciated.

GillyB

 

[dave3009]

Hi GillyB, welcome to the board.

What is the format of the cell before you do the search/replace?

 

[GillyB]

Hi GillyB, welcome to the board.

What is the format of the cell before you do the search/replace?

Hi!

The format of the cell is 01.04.2016 but when I run the Macro it changes it to 04/01/2016.

I did post the wrong section of my code in the initial post, I included the next part which takes away blank spaces in column 'G'.

This is the only part of the Macro which deals with the dates.

Workbooks("Save Here.xls").Worksheets("Save Here").Range("H:I").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

P.S. I am on Excel 2003 with Windows XP. I'd be using a more up-to-date version at home, but this is all I have in the office.

 

[dave3009]

Hi GillyB

Assuming it will only be dates in columns H and I try this:

With Workbooks("Save Here.xls").Worksheets("Save Here")
    .Range("H:H").TextToColumns Destination:=Range("H:H"), DataType:=xlDelimited, FieldInfo:=Array(1, 4)
    .Range("I:I").TextToColumns Destination:=Range("I:I"), DataType:=xlDelimited, FieldInfo:=Array(1, 4)
End With

 

[GillyB]

Hi GillyB

Assuming it will only be dates in columns H and I try this:

With Workbooks("Save Here.xls").Worksheets("Save Here")
    .Range("H:H").TextToColumns Destination:=Range("H:H"), DataType:=xlDelimited, FieldInfo:=Array(1, 4)
    .Range("I:I").TextToColumns Destination:=Range("I:I"), DataType:=xlDelimited, FieldInfo:=Array(1, 4)
End With

Thank you so much for your help, that worked a treat!

 

[dave3009]

Thanks for the feedback, glad it worked