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

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

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