Sorting in Excel

[sjh]

Hi,
I used the following code in a module to sort the fields in a sheet named 'mySheet'. But I get run-time error 1004: method 'range' of object '_worksheet' failed. What's wrong with my code??

mySheet.Range(Cells(8, 1), Cells(84, 30)).Sort Key1:=mySheet.Range("A10&quot, Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Thank you!

 

[SkipVought]

Hi,

Range(mySheet.Cells(8, 1), mySheet.Cells(84, 30)).Sort
...

assuming that mySheet is a worksheet object

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

...in a sheet named 'mySheet'....

You need to learn and understand the difference between the worksheet Object and the worksheet Name.

The sort method must be applied to the worksheet Object.

Try this and see if you can see the difference:
[blue]

Sub Sort1()
  With Worksheets("mySheet")
    .Range(Cells(8, 1), Cells(84, 30)).Sort _
       Key1:=.Range("A10"), _
       Order1:=xlAscending, _
       Header:=xlGuess, _
       OrderCustom:=1, _
       MatchCase:=False, _
       Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal
  End With
End Sub

[/color]
Or, if you prefer, you can do it this way:
[blue]

Sub Sort2()
Dim mySheet As Worksheet
  Set mySheet = Worksheets("mySheet")

[/color]

  mySheet.Range(Cells(8, 1), Cells(84, 30)).Sort Key1:=mySheet.Range("A10"), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

[blue]

  Set mySheet = Nothing
End Sub

[/color]
The second method uses your code as posted. The only difference is it defines mySheet as a Worksheet Object so that the sort can be executed.

Incidently, if you put the line
[blue]

  Option Explicit

[/color]
as the first line in your code modules, you can (usually) get a little more assistance from the compiler. In this case it should have reported that "mySheet" was an undefined variable. That may have led you into finding the solution offered by my second example (where I defined mySheet as a Worksheet).

 

[SkipVought]

Z,

All true EXCEPT...

the Cells() WITHIN Range() must be quallified by the worksheet object.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com