Vba losing date format

[Danny.Smythe]

I created a chase list at work for shop floor but half way through the macro i find and replace . with / . When the macro comes to the end the date format is lost and has become general so the dates are not in order.

Please can some one advise if they is anything i can do .

Best regards
Daniel

[Jack7774]

Are you asking to fix the macro or to recover the spreadsheet because you didn't have a backup?

[Norie]

Why are you doing the find/replace?

[Danny.Smythe]

I am asking for a fix for the macro. i am doing a find and replace because it comes out of SAP with dots.

[Jack7774]

Can you upload the spreadsheet with the macro please?

[Danny.Smythe]

I can get the macro tomorrow as it is at work at the moment .

Kind Regards

[Jack7774]

Ah okay. Until then. By chance are your numbers in german format from SAP?

[Danny.Smythe]

No the Sap is in English but i have found on some find and replace it moves the column up a row and it changes some of the dates around i.e 02/05/2015 would turn out to be 05/02/2015.

[Jack7774]

It would be good to have a raw file from SAP so that I can choose how to open it and maybe there might be an easier fix for you.

[Danny.Smythe]

currently excel attaches to sap and extracts the data in to a specific folder then opens it. and copys in to a file.

[Jack7774]

Something I found on SAP and Excel, not sure if this helps or not until you can get back to work and upload the file or spreadsheet though.

http://scn.sap.com/docs/DOC-31015

[Danny.Smythe]

I will get the excel macro file and upload tomorrow thanks Jack

[Norie]

Instead of using Find/Replace you could try using Data>Text to columns... on the columns that have dates.

[Danny.Smythe]

When i use text to columns it moves the rows up one i don't understand why.

[Norie]

Have you checked the destination on 3rd step when using Data>Text to columns...?

[Danny.Smythe]

Thanks i will test at work tomorrow but the field still losses the date format.

[Danny.Smythe]

I will test again tomorrow but it still loses the date format.

[Danny.Smythe]

Please see attachment below they are a few modules in the book.


Kind Regards

[Jack7774]

No data there to convert though just macro. After thought though once you find and replace the . with / couldn't you setup the macro to multiply those dates by 1 and convert them from text to number because that is ultimately why excel doesn't recognize them as dates. For me at least without seeing the issue or creating it myself I can't help anymore to solve this problem. These other guys like norie will most likely be able to help though.

[Danny.Smythe]

They wont be any data because it is directly taken from Sap.

Kind regards