Dates randomly chaning format (VBA)

[JamesW]

Hi guys,

I have a spreadsheet which is populated by an SAP report. I made a macro to make changes to the sheet and make it look pretty etc.

All works well, apart from the dates seem to switch to US format randomly:

07.02.2011 changes to 02/07/2011
01.05.2011 changes to 05/01/2011
15.08.2011 stays the same (but with /'s)

Here's the problematic bit of my code:

        With Range("F:F")
            .Replace What:=".", Replacement:="/" 
            .NumberFormat = "dd/mm/yyyy"
        End With

Why doesn't that do what it's supposed to?! Removing .NumberFormat makes no difference aswell.

The original dates are stored as Text when they are pulled from SAP (Not sure if this would change anything).

 

[RoryA]

Were the cells formatted explicitly as Text originally? What are your regional settings for dates?

 

[JamesW]

Basically, I pull the report from SAP and they are set as TEXT format by default.

My regional settings are English (UK).

 

[RoryA]

What if you move the NumberFormat line above the other lines?

 

[JamesW]

Hi Rory,

Sorry for the delay in responding, but just realised this is linked to my other post.

If I move the NumberFormat above the other lines then it ****s up my pivot:

http://www.mrexcel.com/forum/showthread.php?t=554359