Excel macro issue

[koobro]

I created this macro:

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 4), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
Cells.Select
Selection.Columns.AutoFit
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Range("E1").Select
ActiveCell.FormulaR1C1 = _
"=DATE(YEAR(R[1]C[-1]),MONTH(R[1]C[-1]),DAY(R[1]C[-1]))"
Range("E1").Select
Selection.NumberFormat = "d-mmm-yy"
Selection.AutoFill Destination:=Range("E1:E925")
Range("E1:E925").Select
Range("I4").Select
End Sub

Before running the macro:
Column "D" had date format "dd-mm-yy".
After running the macro it swapped column "D" date format to "mm-dd-yy" outputting wrong result to column "E"
I've checked that date format in regional settings is dd-mm-yy.

Guys any idea what is wrong? Or how to solve this problem?

Rgds,

Koobro

 

[xlbo]

Please post VBA questions in the VBA forum: Forum707

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question