Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate name range error when copying sheet (Excel 2010) | Excel Programming | |||
Copied sheet causing duplicate range name error (Excel 2010) | Excel Discussion (Misc queries) | |||
Excel 2010 error | Excel Discussion (Misc queries) | |||
Form 4 Range of time from 1/20/2010 4:00 AM To 1/21/2010 10:00 AM | Excel Worksheet Functions | |||
Excel 2003 - VB Code - Run time error....pls help :( | Excel Programming |