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).

 

[JamesW]

Bump!

I recorded myself doing Ctrl+H - Replace . with / and copied the result in place of my current bit of code. The manual replace worked fine (didn't change the dates to US) but the macro does...

How on earth can it work for me manually, but not in VBA?

Confusion ++

 

[Sektor]

Use CDate function.

 

[JamesW]

Makes no difference at all..

 

[RoryA]

It's the joy of VBA being US-centric. You need something like:

   Dim varDates
   Dim lngIndex As Long
   With Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
      varDates = .Value
      For lngIndex = LBound(varDates) To UBound(varDates)
         varDates(lngIndex, 1) = CDate(Replace(varDates(lngIndex, 1), ".", "/"))
      Next lngIndex
      .Value = varDates
      .NumberFormat = "dd/mm/yyyy"
   End With

note there is no checking for blank cells/text/non-dates etc.

 

[JamesW]

note there is no checking for blank cells/text/non-dates etc.

How would I ignore blanks (I could use a FOR loop, but wondered if there was a way using your code)?

 

[RoryA]

Like so:

   Dim varDates
   Dim lngIndex As Long
   With Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
      varDates = .Value
      For lngIndex = LBound(varDates) To UBound(varDates)
         If Len(varDates(lngIndex, 1)) > 0 Then varDates(lngIndex, 1) = CDate(Replace(varDates(lngIndex, 1), ".", "/"))
      Next lngIndex
      .Value = varDates
      .NumberFormat = "dd/mm/yyyy"
   End With

 

[JamesW]

Thanks Rory. It still put them as US dates, so I got rid of CDate and it's now working fine!

Appreciate your help.

 

[RoryA]

That makes no sense at all to me. If you don't use CDate you should get the same behaviour you had originally.

 

[JamesW]

Yeah that's what I thought... I have no idea why it's doing what it's doing, but it seems to be working.

When I ran your code (with CDate) all of my dates were US (rather than random ones), also they were in this format: 4/18/2011 rather than 04/18/2011. Removing CDate seems to work (dates are 18/04/2011).

Could it have anything to do with the original data - It's stored as text.