Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: how to make a sort macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't edit a sort macro which sort a column in worksheet, my code like this:

    Worksheets(Worksheets("txtChecker").Cells(5, 2).Text).Select
    Worksheets(Worksheets("txtChecker").Cells(5, 2).Text).Rows(Worksheets("txtChecker").Cells(1, 2).Text & ":" & Worksheets("txtChecker").Cells(2, 2).Text).Select
    Selection.Sort Key1:=Range("A" & Worksheets("txtChecker").Cells(1, 2).Text), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

    ==============
    Worksheet(txtChecker) is using for user writting their sort row and column and worksheet. But it doesn't work at all and the error msg is:

    Run-time error '1004':
    The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-15 21:37, beeleegie wrote:
    I can't edit a sort macro which sort a column in worksheet, my code like this:

    Worksheets(Worksheets("txtChecker").Cells(5, 2).Text).Select
    Worksheets(Worksheets("txtChecker").Cells(5, 2).Text).Rows(Worksheets("txtChecker").Cells(1, 2).Text & ":" & Worksheets("txtChecker").Cells(2, 2).Text).Select
    Selection.Sort Key1:=Range("A" & Worksheets("txtChecker").Cells(1, 2).Text), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

    ==============
    Worksheet(txtChecker) is using for user writting their sort row and column and worksheet. But it doesn't work at all and the error msg is:

    Run-time error '1004':
    The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.
    Hi,

    I think there are a number of things going wrong with your macro, not the least of which is your reference to the rows you select.

    This seemed to work for me, although you will have to be the judge against real data:

    ---begin VBA---
    Sub test()
    Dim strFileName As String
    Dim RowStart As Long, RowEnd As Long, temp As Long

    strFileName = Worksheets("txtChecker").Cells(5, 2)
    RowStart = Worksheets("txtChecker").Cells(1, 2)
    RowEnd = Worksheets("txtChecker").Cells(2, 2)

    If RowStart > RowEnd Then
    temp = RowEnd
    RowEnd = RowStart
    RowStart = temp
    End If

    With Worksheets(strFileName)
    .Range(.Rows(RowStart), .Rows(RowEnd)).Sort Key1:=.Range("A" & RowStart), _
    Order1:=xlAscending, Header:=xlNo

    End With
    End Sub
    ---end VBA---

    Please note that I gave the sort Header:=xlNo, where the original was Header:=xlGuess. The macro recorder does a poor job of identifying this and I recommend you select one (xlYes or xlNo), if only to make debugging easier.

    HTH,
    Jay


    [ This Message was edited by: Jay Petrulis on 2002-04-15 22:26 ]

    [ This Message was edited by: Jay Petrulis on 2002-04-15 22:28 ]

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One other thing...your macro will only sort on column A. It will bring all the data along, as the whole row is captured, but you can give the user a column choice to sort on as well.

    Just an idea.

    Bye,
    Jay

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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