using Text to columns in a macro

[dippy]

Hi there,

I a have a list of dates which have been imported as MM/DD/YYYY and
am having problems using text to columns in a macro to convert them t
DD/MM/YYYY.

When I do it manually from the data menu it works fine selecting th
data type as Date and MDY.

I can record this and then run the macro and it picks up the date
which are 07/24/05 but ignores the dates which are 08/02/05 (this is
August 05).

This is the script I am using:

Columns("I:I").Select
Selection.TextToColumns Destination:=Range("j1")
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False
FieldInfo _
:=Array(1, 3)

Can anyone help me?

Thanks
:confused

 

[Guest]

Hi Dippy,
I don't the specifics about the text to columns issue.
However, if you need a work around, here is a bit of code that will convert
MM/DD/YYYY to DD/MM/YYYY.

Sub Convert()
Dim Iloop As Integer
Dim Numrows As Integer
Numrows = Range("A65536").End(xlUp).Row
For Iloop = 1 To Numrows
Cells(Iloop, "A") = Mid(Cells(Iloop, "A"), 4, 2) & "/" & _
Left(Cells(Iloop, "A"), 2) & "/" & Right(Cells(Iloop, "A"), 5)
Next Iloop
End Sub

This code assumes that every date is ten characters long and resides in
column A.
You can change the code from column A to the appropriate column reference.
If your date entries vary in length, post back and I will change the code to
allow for that.

HTH