how to make a sort macro

[beeleegie]

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.

 

[Jay Petrulis]

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

 

[Jay Petrulis]

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