Help: Text to Columns & MDY

[Dave]

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

 

[Gary''s Student]

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

 

[Gord Dibben]

Your code works for me.

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


Gord Dibben MS Excel MVP