Ossiemac,

Sorry I missed your reply, I don't seem to be getting notifications.

I have data that comes from an accounting/job costing system that generates
Excel files. Unfortunately, the data can randomly change type a few hundred
lines into the data. I changed the the date format in the accounting
software to dd.mm.yy to avoid ambiguity but some dates still change to MDY
for those that can resolve while others don't. For example, 12 Sept will
change to 9 Dec while 24 Sept stays the same. Some of the numerical columns
will change to text. It was easier for me to coerce the colums I needed for
calculations on every instance.

While this piece of code works fine when only run once, I occassionally need
to make a change to the data sheet (add a digit to the job number to prevent
overwritting the original when saved). I just can't work out why it toggles
between formats while a macro recording keystrokes to convert a single column
dosen't.


--
Jim


"OssieMac" wrote:

Hi Jim,

I have looked at this for ages and maybe I am missing something. You say it
fixes the dates and I interpret this to mean in all columns.

The following array:-
TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1)
only has the first element populated with a 4 for DMY dates so I should
think that only the first column is coerced to recognise the data as a date.

What I really do not understand is what is it about the data in each column
that you can not simply format the columns to "d/m/yy" date format because it
appears that you are using text to columns on each individual column.

Regards,

OssieMac





"Jim G" wrote:

I have the following code that updates a sheet on change to convert (fix)
dates to DMY. Occassionally the sheet will be updated again if the user
makes a second update (an infrequent event). On the second update the format
will change to MDY.

I tested this by repeatedly running the macro and can see the dates toggle
back and forth. Does anyone have any idea why or how I can fix it to DMY no
matter how many times it's activated?

Dim ColsToFix As Variant
Dim TypeOfCols As Variant
Dim iCol As Long

ColsToFix = Array("G", "E", "C", "K", "M", "Q", "R", "S", "U", "W")
TypeOfCols = Array(4, 1, 1, 1, 1, 1, 1, 1, 1, 1)

If UBound(TypeOfCols) < UBound(ColsToFix) Then
MsgBox "design error!-Cols & Types not matched"
Exit Sub
End If

With ActiveSheet
For iCol = LBound(ColsToFix) To UBound(ColsToFix)
.Cells(1, ColsToFix(iCol)).EntireColumn.TextToColumns _
DataType:=xlDelimited, _
FieldInfo:=Array(1, TypeOfCols(iCol))
Next iCol
End With
--
Jim