Combat Womble
New Member
- Joined
- Nov 18, 2015
- Messages
- 30
Hello
I recieved a load of files and use macros to sort clean etc before add the data to other files. These files are then used for data analysis. The date can be an important part of this analysis. I have descovered an error in which the date isn't a date and when the macro sorts a sheet by date the result is incorrect.
So, the error occurs when the input date is a cell that looks like 12-12-2015 (from now on I am going to call it a hyphenated cell), when the input cells look like 12/12/2015 (from now on I am going to call it a slash cell) everything works fine.
Before the macro starts, when I click on the hyphenated cell, excel tells me its a date (home ribbon, number). After the macro has finished excel still tells me its a date, but it has not been ordered by date (unlike the others). Also if i expand the column, then the hyphenated cells are left justified and the slash cells are right justified.
I have tried the following:
1. replace the hyphens with slashes
2. changing the cell format
3. doing 1 and 2
4 remove contents of cell, change cell format add date
Any pointers in the right direction would be amazing and appreciated
edit:
i forgot to mention that I tried selecting the column and changing the format to date. I did this by recording a macro and copying its code.
Combat Womble
I recieved a load of files and use macros to sort clean etc before add the data to other files. These files are then used for data analysis. The date can be an important part of this analysis. I have descovered an error in which the date isn't a date and when the macro sorts a sheet by date the result is incorrect.
So, the error occurs when the input date is a cell that looks like 12-12-2015 (from now on I am going to call it a hyphenated cell), when the input cells look like 12/12/2015 (from now on I am going to call it a slash cell) everything works fine.
Before the macro starts, when I click on the hyphenated cell, excel tells me its a date (home ribbon, number). After the macro has finished excel still tells me its a date, but it has not been ordered by date (unlike the others). Also if i expand the column, then the hyphenated cells are left justified and the slash cells are right justified.
I have tried the following:
1. replace the hyphens with slashes
VBA Code:
ActiveCell.value = Replace(ActiveCell.Value, "-", "/")
Code:
Selection.NumberFormat = "dd/mm/yy"
'and'
Selection.NumberFormat = "m/d/yyyy"
'and'
ActiveCell.NumberFormat = "dd/mm/yy;@"
Code:
Selection = Format(Replace(ActiveCell.Value, "-", "/"), "dd/mm/yyyy")
Code:
If InStr(ActiveCell.Value, "-") > 0 Then
arrayA = Split(ActiveCell, "-")
ActiveCell.ClearContents
ActiveCell.NumberFormat = "dd/mm/yy;@"
ActiveCell.Value = arrayA(0) + "/" + arrayA(1) + "/" + arrayA(2)
End If
Any pointers in the right direction would be amazing and appreciated
edit:
i forgot to mention that I tried selecting the column and changing the format to date. I did this by recording a macro and copying its code.
Combat Womble