using Text to columns in a macro
 

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

--
dipp
-----------------------------------------------------------------------
dippy's Profile: http://www.excelforum.com/member.php...fo&userid=2592
View this thread: http://www.excelforum.com/showthread.php?threadid=39291

using Text to columns in a macro
 

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
--
Ken Hudson


"dippy" wrote:


Hi there,

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

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

I can record this and then run the macro and it picks up the dates
which are 07/24/05 but ignores the dates which are 08/02/05 (this is 2
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:


--
dippy
------------------------------------------------------------------------
dippy's Profile: http://www.excelforum.com/member.php...o&userid=25921
View this thread: http://www.excelforum.com/showthread...hreadid=392918