Hi All,
I have a column of 'dates' in Excel, and visually, they are in the US format eg: 12/31/1995
Try as I might, I can't get them to reformat to UK format : eg 31/12/1995. The oft suggested "Text to Column" (DMY) function to convert them worked for only part the column, but 1139 dates won't budge. The data is in Column A, Rows 1 to 1139.
So I found a line of VBA that works on one row:
As you can see that just fixes A1.
Result: A1 containing 12/31/1995 becomes 31-Dec-1995 in B1)
(Column B, was set-up & formatted to Date format dd-mmm-yyyy prior to running code
How do I expand that code so that every row A1 to A1139 is fixed? I guess loop sort of thing
I did try this but didn't work - type mismatch on line 7
Sub RangeTTC()
Dim rng As Range
Dim row As Range
Dim cell As Range
Set rng = Range("a1:b1139")
For Each row In rng.Rows
For Each cell In row.Cells
[b] = Format([a], "dd-mmm-yyyy")
Next cell
Next row
End Sub
Display More
Many thanks for all help!
Cheers Anniebnz