Macro doesn't repeat manual actions

Hi all!

Have some problem with the macro and excel file.

Basically, what I got from my report is contained in file "1.xls" I've attached.
As you can see, date is in US format, but I need it in UK format. Problem is that Excel thinks its already in UK, so just changing format of the cell doesn't work.

So what I do is I start recording macro. Then I select whole column A and replace "-" with "/". The I do text>columns with devider "/" to break month, year day in 3 columns, so now in AA we have month, in AB we have day, in AC we have year.
Then I put in A2 following formula:

=IF(AA2<>"", IF(AB2>9, IF(AA2<10, AB2&"/"&"0"&AA2&"/"&AC2,AB2&"/"&AA2&"/"&AC2),IF(AA2<10, "0"&AB2&"/"&"0"&AA2&"/"&AC2,"0"&AB2&"/"&AA2&"/"&AC2)),"")

which places date and month and year in correct order and if there is one digit in any of them, place "0" in the begining, so it has "dd/mm/yyyy" format.

Now I copy the formula to all cells in A column and stop recording macro.
As you can see, when done manually it works fine (2.xls is result).

However, when I try to run just-recorded macro on 1.xls, it gives wrong result (some cells have mm/dd/yyyy format and some dd/mm/yyyy). Doing macro step-by-step, I could see that when it replace "-" with "/", it also swaps first and second pair of digits, and it causes wrong result (3.xls).

But when all actions are done manually, its all perfectly OK.

So any ideas why is that, and how to solve it?

Thanks!

Hi

Format column B of 1.xls as text, then try

Please Login or Register  to view this content.

Does that do the full converstions for you???

rylo

rylo, thanks! it works perfect!