Inconsistent Sort

[bgetson]

I'm working with sorting various subsets of data with Excel's
autosort. Because I want the same process done on 4 different
worksheets, I currently have an outer For Each loop to cycle through
the worksheets and an inner to cycle through the row numbers that I'm
dealing with.

For Each sh In Collect
With sh
temp = 2
For Each i In CollectSys
.Range("B" & temp & ":AL" & i).Sort key1:= _
.Range("I" & temp & ":I" & i), order1:=xlAscending
temp = i + 1
Next
End With
Next

Collect is my worksheets, and CollectSys is my row numbers (7, 13, 16,
21, 26 for this example).

About 30% of the time, this works perfectly. That other 70%, it looks
like the process cycles through the inner For loop repeatedly, messing
with the variables (setting temp higher than the highest row number,
instead of resetting to 2). This eventually causes my entire worksheet
to sort ascending, instead of each separate group.

Any ideas why this loop is so inconsistent?
-bgetson

 

[Jim Cone]

Maybe something closer to this...
'--
Sub SSSS()
Dim sh As Worksheet
Dim i As Long
Dim N As Long
Dim temp As Long
Dim CollectSys As Variant

CollectSys = Array(7, 13, 16, 21, 26)
For Each sh In Worksheets
With sh
temp = 2
For N = 0 To UBound(CollectSys)
i = CollectSys(N)
.Range("B" & temp & ":AL" & i).Sort _
key1:=.Columns("I"), order1:=xlAscending
temp = i + 1
Next
End With
Next
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



I'm working with sorting various subsets of data with Excel's
autosort. Because I want the same process done on 4 different
worksheets, I currently have an outer For Each loop to cycle through
the worksheets and an inner to cycle through the row numbers that I'm
dealing with.

For Each sh In Collect
With sh
temp = 2
For Each i In CollectSys
.Range("B" & temp & ":AL" & i).Sort key1:= _
.Range("I" & temp & ":I" & i), order1:=xlAscending
temp = i + 1
Next
End With
Next

Collect is my worksheets, and CollectSys is my row numbers (7, 13, 16,
21, 26 for this example).

About 30% of the time, this works perfectly. That other 70%, it looks
like the process cycles through the inner For loop repeatedly, messing
with the variables (setting temp higher than the highest row number,
instead of resetting to 2). This eventually causes my entire worksheet
to sort ascending, instead of each separate group.

Any ideas why this loop is so inconsistent?
-bgetson

 

[bgetson]

Thanks. It looks like everything's working fine since I've changed my
Collection of numbers to an array. I still don't know why the other
was causing me problems though.

Thanks again for the help.