I use the code below to select a column of data and replace . with a /
so I can sort the data in chronological order (as a date). However,
this only works if I manually find and replace (with the find and
replace dialogue). If I use the macro below, the dates are sometimes
adjusted (day and month is switched). Also, when the data is then
sorted, it does not get sorted in chronological order (only when it is
done manually does the data sort correctly).

The original data is an extract from one of our work databases (which
I have no hope of amending).
Not sure if really the workbook needs to be viewed, but not sure how I
can post this to the group?
Any ideas?

Matt

Sub Change_To_Date_Format()

Dim C As Range
Dim EndCell As String

Range("J3").Select


EndCell = ActiveCell.End(xlDown).Address

Range("J3", EndCell).Select

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False


End Sub

Hi Matt

Try to format column J as Date and run the sub afterwards.

Regards,

Per


"MJKelly" skrev i meddelelsen
...
I use the code below to select a column of data and replace . with a /
so I can sort the data in chronological order (as a date). However,
this only works if I manually find and replace (with the find and
replace dialogue). If I use the macro below, the dates are sometimes
adjusted (day and month is switched). Also, when the data is then
sorted, it does not get sorted in chronological order (only when it is
done manually does the data sort correctly).

The original data is an extract from one of our work databases (which
I have no hope of amending).
Not sure if really the workbook needs to be viewed, but not sure how I
can post this to the group?
Any ideas?

Matt

Sub Change_To_Date_Format()

Dim C As Range
Dim EndCell As String

Range("J3").Select


EndCell = ActiveCell.End(xlDown).Address

Range("J3", EndCell).Select

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False


End Sub