M
MJKelly
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
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