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

 

[SkipVought]

Hi,

Why do Dates and Times seem to be so much trouble? faq68-5827

The Date VALUE is what is the MOST important. The FORMAT is what you see that you are accustomed to and can be any number of different formats for the same DATE VALUE.

You can assign the format in a range using the NumberFormat property of the range object.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

When using VBA, you are better off working (as Skip has mentioned) with date values and then format them once they are in the spreadsheet...

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

 

[combo]

Are you sure that input date format (in string in column "A") is DMY? If it is MDY, you have to change FieldInfo col. 4 parameters: Array(4,3).
BTW, what you do next is to split data into parts and complete it again, the same, but shorter in E1: "=D2".

combo

 

[SkipVought]

The parsing spec merely tells the parser how to interpret the SOURCE STING. Has nothing to do with the RESULT FORMAT.

Skip,
[sub]
[red][/red]
[/sub]