Thread: VB Sort with variables in the set range property

Excel 2007 VB 6.5
I'm trying to use variables (defined) to set the range in the following example instead of actual cell (r,c) references. It does not work , I have tried various ways, it faults out on the line ".SetRange = ActiveSheet.Range(Cells(BrakeSheetPullRow, 2), Cells(BrakeSheetPullRow + 10, uCol)).Select 'ActiveSheet.Range(Cells(5, c1), Cells(20, c2))
If I substitute .SetRange Range("B17:IQ20") the code works.


However when the program gets to the line ; “ActiveWorkbook.Worksheets("Brakes").Range(Cells(BrakeSheetPullRow, 2), Cells(BrakeSheetPullRow + 10, uCol)).Select “
It highlights the range demonstrating that it recognises it…..

The entire relevant code is:

Dim BrakeSheetPullRow ‘definition

BrakeSheetPullRow=2

……………………………………………………………………………………………………….
ActiveWorkbook.Worksheets("Brakes").Range(Cells(BrakeSheetPullRow, 2), Cells(BrakeSheetPullRow + 10, uCol)).Select 'ow+10,uCol)) ‘was Worksheets("Brakes").Range("B17:IQ20").Select
'ActiveWorkbook.Worksheets("Brakes").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Brakes").Sort.SortFields.Add Key:=Range(Cells(BrakeSheetPullRow + 10, 2), Cells(BrakeSheetPullRow + 10, uCol)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Brakes").Sort
.SetRange = ActiveSheet.Range(Cells(BrakeSheetPullRow, 2), Cells(BrakeSheetPullRow + 10, uCol)).Select ' sample code; ActiveSheet.Range(Cells(5, c1), Cells(20, c2))
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

I get run time error; 1004; Application defined or object defined error

Can anybody shed light on why this does not work?
I get run time error; 1004; Application defined or object defined error

You have dimensioned BrakeSheetPullRow as a Variant with the code

Dim BrakeSheetPullRow 'definition

It should be dimensioned as Long. I also do not see uCol being set a value and again it should be dimensioned as Long. The type mismatch is due to the setrange requiring numbers not variants/arrays
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Have you tried - oh and by the way can you have a read of the forum rules - all your code must be included in code tags - have a look at http://www.excelforum.com/misc.php?do=bbcode#code

.SetRange Range(Cells(BrakeSheetPullRow, 2), Cells(BrakeSheetPullRow + 10, uCol))
'or you could try
.SetRange Range("B2").Resize(10, ucol - 2)

If that does not work can you post a sample workbook with no sensitive data and include the code as well - your full code listing for this sort. There is no reason why you cannot use variables to set the range to sort.

Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

Anthony,
It look like I have resolved the problem myself.
The solution was to use relative references in the code by depressing the "Use Relative References" button prior to running the code.
Thank you for your input I will report any further issues

So the rule is when using variables in the set range property "use relative references" in the spreadsheet.
One also need to set this property when opening the macro enables workbook for if you set it later the SetRange property will not work.

Funny how the select and Key:=Range properties in the same piece of code do not need this setting