Run time error specifying range for sort code vba excel 2010
 

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 so if anyone has any ideas hpw I might reference ther desired range (range A1 to A6 but I cannot use that notation as there is a variable) I would appreciate any suggestions.

Regards, Mark

Run time error specifying range for sort code vba excel 2010
 

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(A ctiveWorkbook.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(ActiveW orkbook.Worksheets("Results").Cells(1, 1), ActiveWorkbook.Worksheets("Results").Cells(Variabl e, 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

Run time error specifying range for sort code vba excel 2010
 

It happens that dial13 formulated :
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 so if
anyone has any ideas hpw I might reference ther desired range (range A1 to A6
but I cannot use that notation as there is a variable) I would appreciate any
suggestions.

Take off some "."
.SetRange Range(Cells(1, 1), Cells(Variable, 1))

Bruno