Number store as text & text date with 2 digit year problem

[obc1126]

Hi there,

I have a macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table.

The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.

Is there something missing in my code?


I attach here with the my workbook for reference.

Thanks!!

[JBeaucaire]

Highlight the date cells. Click on Data > Text to Columns > Next > Next > Column Date = Date: MDY > Finish

Voila.

You can add that step right into your import macro to fix it on the fly.

Please Login or Register  to view this content.

[stanleydgromjr]

JBeaucaire,

Nicely done.

We can always learn something new.


Have a great day,
Stan

[JBeaucaire]

If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

[Trebor76]

Hi there,

If you replace (or comment out) these lines of code in the FormatData macro...

Please Login or Register  to view this content.

...with this

Please Login or Register  to view this content.

your procedure should run fine - note though that the dates in the range E49 to E55 are not in your lookup table in the Info tab.

HTH

Robert

[obc1126]

Thanks JBeaucaire & Robert.

This solved the date format issue. I have made some adjustment on Robert code and the issue on column B&C are also solved.

Millions thanks to you all.

Merry Christmas & Happy New Year.