Strange find and replace query
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 |
Strange find and replace query
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 |
All times are GMT +1. The time now is 06:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com