Text to Columns VBA date format error

Hi all,

I'm writing a VBA procedure which involves using text to columns on a data set to make the data the right format (all the data is formatted as text initially). For each column I am using the 'General' data type which is selected in step 3 of the text to columns wizard.

The issue I am having is with columns containing dates. When I run the vba procedure some of the dates will get formatted to dates (aligned right in their cells) but others will be left as text format (aligned left in their cells). This only happens with the vba procedure though - if done manually using text to columns they all convert correctly.

Please see the dummy file I have attached as illustration. The procedure Macro 1 is just a recording of me performing the text to columns function. When done manually the data converts correctly, when the procedure is run the data does not convert correctly.

By the way I am aware that I can change the data type in the array segment of the code to tell text to columns to format as date. However with my data set the columns can be in varying order and identifying which columns are meant to be dates is potentially a lot more coding.

Thanks.

Darn. Thanks for the info.