Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    VBA-Excel 2003 (Date Order Problem)

    Hi,

    Im currently putting together a macro that one of its tasks is to put the data in date order, the dates being in column E.

    The relevant code is below;

    Range("E:E").Sort Key1:=Range("E1"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

    Ive previously, coded to set the column to autofilter.

    For some reason the dates are getting into line?

    Im running out of ideas having tried various things. Has anyone got any pointers please??

    Many Thanks.
    Lee

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

    Re: VBA-Excel 2003 (Date Order Problem)

    What do you mean by "For some reason the dates are getting into line?"

  3. #3
    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: VBA-Excel 2003 (Date Order Problem)

    I can see it being a problem if they were getting out of line, but I don't understand otherwise. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    Sorry, its a typo, I was a bit rushed when I typed it.

    I meant to say - "For some reason the dates aren't getting into line?" - Meaning they aren't behaving.

    Basically the dates arent going into date order, 'Ascending' or come to that 'Descending'

    I need the data to be ordered in date order.

    Sorry for the confusion.

    Regards.
    Lee

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

    Re: VBA-Excel 2003 (Date Order Problem)

    Are they real data values, or perhaps text values looking like dates? They'd be ordered alphanumerically if they were text values.

    If you wish, you can post (a stripped down copy of) your workbook.

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    Hi Hans,

    Im not sure Im afraid. Im under the impression that they are real date values, but its probably that, that is causing the problem.

    I attach a copy of the workbook as requested.

    Regards.
    Lee
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    If you look closely at your dates you will see that some of them are right aligned, and some are left aligned.

    The ones that are aligned to the left have been entered as text, not as dates.

    StuartR

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

    Re: VBA-Excel 2003 (Date Order Problem)

    Column E contains a mixture of date and text values. You can easily see this by increasing the column width - you'll see that some values will be right-aligned (the real date values) and some left-aligned (the text values masquerading as dates).

    A simple way to convert them all to date values:
    - Select column E.
    - Activate the Visual Basic Editor.
    - Activate the Immediate window (Ctrl+G).
    - Type selection.value = selection.value and press Enter.

    You should then be able to sort the values correctly.

  9. #9
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    Thanks for that.

    Unfortunately when I tried nothing really changed. I then inserted your suggestion into the code as follows;

    For iLoop = 1 To 100
    If StrFe(iLoop) <> "" Then
    Selection.AutoFilter Field:=1, Criteria1:=StrFe(iLoop)
    Rows("1:1").Select
    Selection.Font.Bold = True
    Cells.Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Select
    ActiveSheet.Name = StrFe(iLoop)
    Range("A1").Select
    ActiveSheet.Paste
    Selection.ColumnWidth = 18
    Columns("A:A").VerticalAlignment = xlCenter
    Columns("A:A").HorizontalAlignment = xlCenter
    Columns("B:B").WrapText = True
    Columns("E:E").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    Rows.AutoFit
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Sheet1").Select
    Application.CutCopyMode = False
    Range("A1").Select
    Columns("A:A").Select
    End If
    Next

    Columns("E:E").Select
    Selection.Value = Selection.Value '## Hans Amendment ##


    Sheets("Sheet1").Select
    Selection.AutoFilter Field:=1
    ActiveSheet.Select
    ActiveSheet.Name = "General"
    Range("A1").Select
    Columns("A:F").Select
    ActiveSheet.Select
    Selection.ColumnWidth = 18
    Columns("A:A").VerticalAlignment = xlCenter
    Columns("A:A").HorizontalAlignment = xlCenter
    Columns("B:B").WrapText = True
    Rows.AutoFit

    ' ****** Deletes sheets not being used *******

    Sheets(Array("Sheet2", "Sheet3")).Select
    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True


    But it still doesnt appear to sort correctly. Im not sure where Im going wrong Im afraid?

    Regards.
    Lee

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

    Re: VBA-Excel 2003 (Date Order Problem)

    I have no way to run that code, since it refers to unknown items, moreover it doesn't even attempt to sort the data. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    There is no need to use a macro to convert the column to date values, you only have to do it once.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    Ok, no problem thanks Hans.

    I'll have a play and see what I can do.

    Can I just double check - I run the line of scipt you mentioned, on the immedate window after Ive run the main macro, or before? Sorry if it might seem obvious, but Im still very much learning and havent used the Immediate window before.

    Many thanks.
    Lee

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

    Re: VBA-Excel 2003 (Date Order Problem)

    I'd execute the line before running the macro, or at least before trying to sort the dates.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: VBA-Excel 2003 (Date Order Problem)

    Ok.

    Many thanks for your help Hans.

    Regards.
    Lee

Posting Permissions

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