Delimit not working

[mikeymay]

I have 2 columns of data that hold dates. Some of the entries have a single quote before the data.

When I use the following script on each column

Range("E2").Activate
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

it will convert all the data in column D, but the quoted code above doesn't work on column E.

When I recorded the code it worked, but when executed in a macro it doesn't seem to recognise the single quote in the second column and doesn't convert to a date.

Does anyone know why this is happening?


Thanks

 

[onlyadrafter]

Hello,

is this any better?

    Range("E2").Select
    Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="'", FieldInfo:=Array(1, 4

)

 

[mikeymay]

Actually just managed to sort the problem as the notification of a reply came thru'.

The data is being copied from an Access database and I was copying over with paste special, text. If I copy as csv the data drops in in the formats I have set in the columns so no need to delimit.

Thanks for looking at it anyway.