Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort Macro (2003)

    I wrote a sort macro and keep getting an error.

    Error message - 'Sort method of Range class failed'.

    This is the macro I wrote.
    Worksheets("Year 1").Range("o6400").sort
    Key1 = Worksheets("Year 1").Range("o6")

    I know something is wrong because VB editor does not automatically capitalize the sort command.

    I have reviewed the help functions and am still getting no where.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    It would help if we could see the whole macro. However, I see several possible problems with what you posted.

    1- If that code is really on two lines, then you need a statement continuation operator (an underline character) at the end of the first line.

    2- When using named parameters, you need to use := (a colon followed by an equal) between the parameter name and the parameter value.

    Try this:

    <code>
    Worksheets("Year 1").Range("o6400").Sort _
    Key1:=Worksheets("Year 1").Range("o6")
    </code>
    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    That is my whole macro

    Sub sort()

    Worksheets("Year 1").Range("o6400").sort _
    Key1:=Worksheets("Year 1").Range("o6")

    End Sub


    VB editor keeps turning the key line red - it does not like it.

    VB editor is still not capitalizing the sort function.

    Other suggestions?

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    Try entering a SPACE between the word sort and the underscore:

    sort _

    (this may be just a matter of the way it shows in the website - as not formated text)

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort Macro (2003)

    I typed all of this into a module using lower case letters. When I hit the [Enter] key, some of the code capitalized. The word "sort" did not. But the code works just fine.
    <pre>Sub sort()

    Worksheets("year 1").Range("e3:e11").sort _
    key1:=Worksheets("Year 1").Range("e3")


    End Sub</pre>

    Note: when you get t to the end of a line, hit the space bar, followed by an underscore and then the [Enter] key to begin the next line.

    This code also works, offers a few additional options and the word "sort" did not capitalize. The code looks odd because I put line breaks in strange places. Did it deliberately to see if it affected the use of the code. It did not. Remember to end a line by hitting the space bar once, then an underscore and the {Enter] key to begin the next line...

    <pre>Sub sort1()
    Worksheets("year 1").Range("e3:e11").sort _
    key1:=Worksheets("year 1").Range("e3"), order1 _
    :=xlAscending, MatchCase:=False, header:= _
    xlNo

    End Sub</pre>


    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    - Ricky

  6. #6
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    I used the macro suggested by Ricky and now I am getting the following error...
    'Run time error 438: Object does not support this property or method'
    Please help. I did not think this was a hard function.

    I have dates coming over that are not in order. I need them in order for the graph I want to create. Do you have a suggestion of how to accomplish this better than the sort macro?

    Thank you.

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

    Re: Sort Macro (2003)

    Perhaps your range contains text values - it's hard to tell without seeing the worksheet. Could you post a copy if it? Remove items not relevant to the problem, and change senstitive data if necessary.

  8. #8
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    Here is the workbook I am working in. I removed unnecessary data. The macro is called sort.

    I would appreciate any help. Thank you.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sort Macro (2003)

    There is nothing in the O column, do you mean:

    Worksheets("Year 1").Range("c5:I400").sort _
    key1:=Worksheets("Year 1").Range("C1")

    Note: you have lots of zeros in column C, so if you sort ascending the zeroes will all come first....

    Steve

  10. #10
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sort Macro (2003)

    There is also nothing in row 6. A list has to be a solid block of data with column headings in the first row of that solid block, else it is not a valid list!
    Regards,
    Rudi

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

    Re: Sort Macro (2003)

    In your workbook, the dates are not in column O but in column C.

    There is a problem with your data: the empty-seeming cells in C6:C400 are not really empty: they contain 0 values, but the custom format hides those. You should get rid of them:
    - Click in one of the empty seeming cells. You should see a strange date in the formula bar.
    - Click in the formula bar.
    - Select the date and copy it to the clipboard (Ctrl+C)
    - Select column C.
    - Select Edit | Replace...
    - Paste the date into the Find what box (Ctrl+V)
    - Make sure the Replace with box is empty.
    - Click Replace All.
    - Close the Replace dialog.

    You don't want to sort only column C, because columns D through I contain related data. You should sort the entire table on column C. Change the macro to
    <code>
    Sub MySort()
    Worksheets("Year 1").Range("C6:I400").Sort _
    Key1:=Worksheets("Year 1").Range("C5"), Header:=xlYes
    End Sub
    </code>
    (I prefer not to name the macro Sort, because that is the name of a built-in method).

    The macro should now work correctly.

  12. #12
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    Is there a way to do a find and replace in the macro before it sorts? I want to find and replace the odd date with blanks.

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

    Re: Sort Macro (2003)

    Try this:
    <code>
    Sub MySort()
    With Worksheets("Year 1")
    .Range("C6:C400").Replace "0", ""
    .Range("C5:I400").Sort _
    Key1:=.Range("C5"), Header:=xlYes
    End With
    End Sub</code>

  14. #14
    New Lounger
    Join Date
    Jun 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sort Macro (2003)

    It worked. The only thing I changed was "0" to "1/0/1900". Because it was replacing every 0 which turned all of my 2006 data to 2026 and the 1/0/1900 was 1//19. Thank you.

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

    Re: Sort Macro (2003)

    Sorry about that. Glad you found the correct solution.

Page 1 of 2 12 LastLast

Posting Permissions

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