VBA - Text to Columns

[ChrisFoster]

Hi,

I have some code which works fine, but I now want to add some coding to utilise the Text to Columns function.
I need column B to be fixed width of 5 characters and the data format to be set as 'Date' (UK date though not US).

Please can someone help?

Cheers

Chris

 

[Joe4]

If you turn on the Macro Recorder, and record yourself performing those steps manually, you will have recorded the VBA code that you require!
So then you can copy/paste it into your other VBA code.

 

[ChrisFoster]

If you turn on the Macro Recorder, and record yourself performing those steps manually, you will have recorded the VBA code that you require!
So then you can copy/paste it into your other VBA code.

Ah yes, I hadn't thought of that. Feel a little bit silly now!

 

[Joe4]

No worries!

It is still early in the morning here. I usually blame a lack of caffeine in the system!

 

[ChrisFoster]

Hmm, that doesnt actually seem to have worked as I had hoped.

In column B there are dates, all of them are from October but the years can vary. When I manually use the Text to Columns function it works fine, but when I use it with the code I've copied and pasted not all the dates pull through correctly. Here is a link to my One Drive that show screen shots. If any one could help I'd appreciate it. https://1drv.ms/u/s!AvMWaXTcbDBtlF8sQtXWvC0h2wbN?e=9oEDWh

The code I'm using is;

    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 4), Array(5, 1)), TrailingMinusNumbers:=True

Cheers

 

[Joe4]

Unfortunately, I am unable to view/download external links form my current location (work security prevents it).
Are you sure that you chose the proper date format in working through Text to Columns? Which one did you choose?

If you could post a few examples directly in your post, showing some that worked, and some that didn't, that would be a big help.

If you like pretty images, there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[ChrisFoster]

Yes I am definitely using the Text to Columns correctly. I get the correct result when doing it manually, but in VBA it brings back some errors.

But, I think I know what the problem is (just don't have a resolution) - when VBA is reading the date it is doing so in US format, not UK. So when it then when it presents the new data that is in US format. I need some how for VBA to read it in UK format.

Any ideas?

 

[Joe4]

If you post some examples of the data, and what the results should look like, maybe we can come up with some alternative VBA methods to di what you want (I will do my best, but since I do not have the UK version, I won't be able to verify that it will work exactly the same way for you).

 

[ChrisFoster]

I am struggling to post some examples other than the screenshots I've used in the previous link. My workplace has some restrictions so I can not download the HTML maker etc.

However, I do think I may have found a way round it (albeit a long winded way).

I want to use the below code to set the dates I have to US format but as text, then let the Text to Columns code run and then convert it back to UK format.

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""mm/dd/yyyy"")"
    
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & Lastrow), Type:=xlFillDefault

I am however having a problem when I'm trying to autofill the formula. I guess it's something very simple, but how do I autofill the formula in cell C2 down to the last row?

 

[ChrisFoster]

Ignore my last post, I've managed to work it out using;

ActiveCell.AutoFill Range(ActiveCell, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1))