Help: Text to Columns & MDY
 

Hi Everyone,

I'm trying to do the following in VB.

Data TextToColumns Delimited 'Delimiters Doesn't Matter' Column Data
Format = Date: MDY.

I recorded a macro manually doing this and came up with the following:


Code:
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
",", FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

This works when I do it manually, but not when part of a macro.

Can anyone help?

Thanks
Dave

Help: Text to Columns & MDY
 

If column A has a list of dates in text form that you want to convert to
"real" dates, then:

Sub dave()
Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each rr In r
On Error Resume Next
rr.Value = DateValue(rr.Value)
skipit:
Next
End Sub
--
Gary''s Student - gsnu200750


"Dave" wrote:

Hi Everyone,

I'm trying to do the following in VB.

Data TextToColumns Delimited 'Delimiters Doesn't Matter' Column Data
Format = Date: MDY.

I recorded a macro manually doing this and came up with the following:


Code:
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
",", FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

This works when I do it manually, but not when part of a macro.

Can anyone help?

Thanks
Dave

Help: Text to Columns & MDY
 

Your code works for me.

What happens or doesn't happen when you run the macro?


Gord Dibben MS Excel MVP

On Tue, 23 Oct 2007 06:23:01 -0700, Dave wrote:

Hi Everyone,

I'm trying to do the following in VB.

Data TextToColumns Delimited 'Delimiters Doesn't Matter' Column Data
Format = Date: MDY.

I recorded a macro manually doing this and came up with the following:


Code:
Columns("A:A").TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=True, Comma:=True, Space:=False, Other:=True, OtherChar:= _
",", FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True

This works when I do it manually, but not when part of a macro.

Can anyone help?

Thanks
Dave