Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: .value = .value screwing date format (2002 SP2)

    Strange - I am using dd-mm-yyyy format on my system, but both macros result in correct values.

  2. #2
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    .value = .value screwing date format (2002 SP2)

    Well, this had me really confused.

    I hope someone can help with a good fix.

    I have a macro called Propagate_Data. It allows me to copy down summarised headings against all rows.

    The original version was:
    Sub propagate_data()
    With Selection
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End With
    Application.CutCopyMode = False
    End Sub

    But I recently started using a newer version:
    Sub propagate_data()
    With Selection
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
    End With
    End Sub

    Everything was going swimmingly, until I I just tried to use the new version against a list of dates.

    The dates in UK format were 01/05/2004, 01/06/2004, 01/07/2004, 01/08/2004 and were formatted on the sheet as May-04, Jun-04, ...

    Now, when I ran the latter version of my propagate_data macro, all the dates were displayed as Jan-04.

    When I examined the actual contents of the cells I am seeing 05/01/2004, 06/01/2004, ...

    The former version of propagate_data works fine, but the latter version, using .value = .value seems to assume that it is either reading from or writing to the US data format.

    Now, whilst I can in this case simply change back to using the older version of propagate_data, there are other macros that I (and I'm sure others) have that use .value = .value.

    So, any ideas as to how to persuade Excel to consistently use the correct date format?

    I have attached a sample file with 2 macros to compare the results.

    Just select one of the 2 columns and run one of the macros, then select the other column and run the second one.

    I suspect that only non-US folks will be able to see the problem descrivbed above.

    Any assistance would be appreciated.

    Futher information found: This only happens if there is more than one cell selected when ".value = .value" is run. If only 1 cell is selected, then the date format is converted correctly, if more than one is selected, then the error appears.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .value = .value screwing date format (2002 SP2)

    That isn't listed in my Region Settings. Mine is set to dd/MM/yyyy

    As I just added to my original post, I only see the problem if more than one cell is selected when I run the macro.

    I have attached pd2.xls which shows the results of running propagate_data1 on a single cell (C3) and on multiple cells (C10 and C11 selected.

    It also has a picture of my regional settings dialog.

    I tried setting my date settings to dd-MM-yyyy which seems to be the nearest that I can get to your dd-mm-yyyy one, but I still see the problem if more than one cell is selected.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: .value = .value screwing date format (2002 SP2)

    Sorry, I can't reproduce this. I set my date format to dd/MM/yyyy, and the result of the macro is correct, whether I run it on one cell or on more cells.

    Could other Loungers with a non-US system date setting please test the workbook attached by Paul?

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: .value = .value screwing date format (2002 SP2

    Paul,
    Both your macros work fine on Excel 2002 SP3 but your description of the problem sounds like it may be related to Q821564 since I imagine the behind the scenes transformation is similar to the one described. It would appear that applying SP3 will cure the issue.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: .value = .value screwing date format (2002 SP2

    I was unable to replicate your problem using Excel 2003 SP1 set up with UK date formats.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: .value = .value screwing date format (2002 SP2

    Good catch! I am using Excel 2002 SP-3 too.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .value = .value screwing date format (2002 SP2

    Cheers Rory,
    the description on the problem at the link that you sent was not exactly what I was seeing, but it sure cured the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •