none
Autosort vba problem RRS feed

  • Question


  • I have a problem with autosort in excel, i want to order one table based on the date of one column,

    Private Sub worksheet_change(ByVal target As Range)
    On Error Resume Next
    Range("D1").Sort Key1:=Range("D2"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlSortColumns

    End Sub
    That code works if i put the dates on the column but the thing is that column have the data based in other one that is on other sheet. When i put that code it doesnt work and looking in internet i saw this one:

    Private Sub WorkSheet_Calculate()
    Columns("D").Sort Key1:=Range("D2"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlSortColumns

    End Sub


    With that one everytime i put new data it goes in a run time error 1004, sort method of Range class failed
    I tried everything i could think of but still the same thing

    Other thing is that the column use dates so when i manual sort it it goes all the blanks one on the top is there any way to dont sort at all the blanks?

    Thx in advance
    Tuesday, February 24, 2015 11:26 PM

Answers

  • Because you say that your first code works then I am making some assumptions without testing. If the code below does not work then I will need an example workbook to test the code.

    When you use Event driven code then it is necessary to suppress the events while the code is running otherwise there will be recursive calls to the event each time the code makes a change that triggers the event. It is then necessary to handle errors in the code to ensure that events are re-enabled after being disabled because they can remain off until you re-start excel or turn them back on with other code. Next if an error does occur then needs a message to the user otherwise they will be unaware that an error ever occurred.

    The first line after the sub name that I commented out can be used to stop the code from running. Often during development you will find this useful. 

    When using sort code it is wise to always specify headers with xlYes or xlNo. (I would avoid xlGuess as you have in the second code example.)

    It is also wise to specify to which worksheet ranges belong. I have used Me which is the generic name for the worksheet to which the VBA module belongs.

    Private Sub Worksheet_Calculate()
        'Exit Sub                           'Uncomment this line to stop code running
       
        On Error GoTo ReEnableEvents        'Ensure events are re-enabled after errors
       
        Application.EnableEvents = False    'Suppress recursive calls to event code
       
        'Me is the generic reference for the worksheet to which the module belongs
        Me.Range("D1").Sort Key1:=Me.Range("D2"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlSortColumns
       
    ReEnableEvents:

        If Err.Number <> 0 Then
            MsgBox "An error occurred in Private Sub Worksheet_Calculate."
        End If
       
        Application.EnableEvents = True
       
    End Sub


    Regards, OssieMac

    • Proposed as answer by L.HlModerator Wednesday, March 4, 2015 8:57 AM
    • Marked as answer by L.HlModerator Thursday, March 5, 2015 2:06 AM
    Wednesday, February 25, 2015 1:55 AM

All replies

  • I don't understand the question. Can you upload an example workbook. Also what version of Excel are you using?

    Guidelines to post a workbook on OneDrive

    1. Zip your workbooks. I prefer that you do not just save to OneDrive. (To Zip a file, in Windows Explorer  Right click on the selected file and select Send to -> Compressed (zipped) folder.)
    2. Go to this link.  https://onedrive.live.com
    3. Use the same login Id and Password that you use for this forum.
    4. Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded and select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    5. Right click the file on OneDrive and select Share.
    6. Do NOT fill in the form; "Select Get a Link" on the left side.
    7. Click the button "Create a Link"
    8. Click in the box where the link is created and it will highlight.
    9. Copy the link and paste into your reply on this forum.

    Regards, OssieMac

    Wednesday, February 25, 2015 12:08 AM
  • Im using excel 2010 and the question is if anyone know a code in vba to autosort a table everytime the data changes.

    The thing is the first code i posted works but only if u manually put the data in the table, if the data is linked to other sheet (and thats the case) it doesnt work at all.

    With the second code when i change or put new data it goes into an error.

    So i want to order one table based in one column that have date data. This data came from another table where ppl put it and i dont know why thats the problem.

    I hope u understand it now, i dont have the worksheet becouse i cant get it out from work.. you know, rules.. Thats funny becouse im the one who made it but its like that. If its necesary i supuse i can make a quick one with the same issues.

    Thanks


    • Edited by Sekerin Wednesday, February 25, 2015 12:31 AM
    Wednesday, February 25, 2015 12:18 AM
  • Because you say that your first code works then I am making some assumptions without testing. If the code below does not work then I will need an example workbook to test the code.

    When you use Event driven code then it is necessary to suppress the events while the code is running otherwise there will be recursive calls to the event each time the code makes a change that triggers the event. It is then necessary to handle errors in the code to ensure that events are re-enabled after being disabled because they can remain off until you re-start excel or turn them back on with other code. Next if an error does occur then needs a message to the user otherwise they will be unaware that an error ever occurred.

    The first line after the sub name that I commented out can be used to stop the code from running. Often during development you will find this useful. 

    When using sort code it is wise to always specify headers with xlYes or xlNo. (I would avoid xlGuess as you have in the second code example.)

    It is also wise to specify to which worksheet ranges belong. I have used Me which is the generic name for the worksheet to which the VBA module belongs.

    Private Sub Worksheet_Calculate()
        'Exit Sub                           'Uncomment this line to stop code running
       
        On Error GoTo ReEnableEvents        'Ensure events are re-enabled after errors
       
        Application.EnableEvents = False    'Suppress recursive calls to event code
       
        'Me is the generic reference for the worksheet to which the module belongs
        Me.Range("D1").Sort Key1:=Me.Range("D2"), _
        Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlSortColumns
       
    ReEnableEvents:

        If Err.Number <> 0 Then
            MsgBox "An error occurred in Private Sub Worksheet_Calculate."
        End If
       
        Application.EnableEvents = True
       
    End Sub


    Regards, OssieMac

    • Proposed as answer by L.HlModerator Wednesday, March 4, 2015 8:57 AM
    • Marked as answer by L.HlModerator Thursday, March 5, 2015 2:06 AM
    Wednesday, February 25, 2015 1:55 AM
  • Thanks! ill try keeping in mind all of that if i still cant manage ill post one worksheet with the same struture and ask again. i prefer to try it first if not ill never learn, if im near to throw the pc over the window again ill post it.

    Thanks :)

    Wednesday, February 25, 2015 2:22 AM