Run time error specifying range for sort code vba excel 2010

[dial13]

Run time error 438 Pbject doesn't support this property or method

Here is my code:

With ActiveWorkbook.Worksheets("Results").Sort
.SetRange .Range(.Cells(1, 1), .Cells(Variable, 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

The problem is with the second line, despite comiling with no rpobelm when I run it it comes up with the above error. it is the last bit of my code soif anyone has any ideas hpw I might reference ther desired range (range A1to A6 but I cannot use that notation as there is a variable) I would appreciate any suggestions.

Regards, Mark

 

[benmcclave]

Mark,

The second line is giving you trouble because the ".Range (.Cells(..." combined with the "With" statement effectively set the "Setrange" argument to:
"ActiveWorkbook.Worksheets("Results").Sort.Range(ActiveWorkbook.Worksheets("Results").Sort.Cells(1,...".

To fix it, add the sheet name in front of ".Range" and ".Cells". This would read:

ActiveWorkbook.Worksheets("Results").Range(ActiveWorkbook.Worksheets("Results").Cells(1, 1), ActiveWorkbook.Worksheets("Results").Cells(Variable, 1)).

To make it easier to read, you could add a "Dim wsResults as Worksheet" statement to the top of your code, then "Set wsResults = ActiveWorkbook.Worksheets("Results")" just below it. Then, instead of the lengthy SetRange statement you could write

wsResults.Range(wsResults.Cells(1, 1), wsResults.Cells(Variable, 1)).

Here is a completed sub to illustrate:

Dim wsResults As Integer
Set wsResults = ActiveWorkbook.Worksheets("Results")
With wsResults.Sort
.SetRange wsResults.Range(wsResults.Cells(1, 1), wsResults.Cells(variable, 1))
.Header = xlYes
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With


Hope this helps,
Ben