Change dates stored as text in table

[kersplash]

Hi,

In the attached I have a table with some dates. Some of the dates are stored as text.

I have recorded a macro (Macro1) that selects the table contents and then uses text to columns to convert the text dates to real dates. The recording works fine.

If I put new dates in the table, once again some are text and try to run the same recorded macro, it doesn't change the dates as expected.

Any ideas?

Please Login or Register  to view this content.

[humdingaling]

change this part specifically
FieldInfo :=Array(1, 1) to FieldInfo :=Array(1, 4)


https://msdn.microsoft.com/en-us/vba...meration-excel

link explains more but its down to this basically
Name Value Description
xlDMYFormat 4 DMY date format.

[kersplash]

change this part specifically
FieldInfo :=Array(1, 1) to FieldInfo :=Array(1, 4)


https://msdn.microsoft.com/en-us/vba...meration-excel

link explains more but its down to this basically
Name Value Description
xlDMYFormat 4 DMY date format.

I changed the code and it now changes the table, but it swaps the day and month for some of the dates???
Capture.JPG

[humdingaling]

also selection not really required

Please Login or Register  to view this content.

[kersplash]

also selection not really required

Please Login or Register  to view this content.

Thanks for the mod.

But still have the same issue as Post #3

[humdingaling]

ok you made me go down a rabbit hole!!
anyways this seems to do what you want

Please Login or Register  to view this content.

because adding zero to text = type mismatch

it loops thru table to see if you get a type mismatch on any of the cells

if it does...only apply text to column that that cell ONLY...i'm not sure if this kills performance but in the small scale nothing to write home about

[kersplash]

ok you made me go down a rabbit hole!!

Hahaha thanks for that. It drives me mad that when you record something it doesn't necessarily do the same thing as the recording.

This works perfectly and I'm only running on small tables of data so performance shouldn't be a problem.

Cheers

[humdingaling]

Cheers thanks for the rep

yes i did notice the manual text to column seems to work which confused me too
i think the problem is that VBA is US centric so once you get into date format.....all sorts of things you need to cater for

the end solution was surprisingly easier than the research i encountered

[kersplash]

the end solution was surprisingly easier than the research i encountered

Always the way.

I've now added this to my multiple sheets and tables with the same issue and works like a charm on my real data size.

Perfect!