james.L
02-19-2020, 03:03 PM
I am trying to run a sort function many times across many ranges but I cannot seem to get the second iteration of the loop to work properly
First data range that needs to be sorted is B108:E126, sort on column E. Next range is H108:K126, sort on column k- offset by 6 columns from the first range
Sortfluxcriteria is the column I'm sorting and sortflux is the total range. The first iteration sorts fine but I can't seem to get the ranges defined correctly so that they move correctly to the second iteration. It completes with the first cycle and does nothing after that or just sorts on the original range twice. Any advice?
Here is my code thus far -
Sub Macro3()
Dim j As Integer
j = 0
Do While j < 12
Dim sortfluxcriteria As Range
Set sortfluxcriteria = Range("E108:E126").Offset(0, j)
Dim sortflux As Range
Set sortflux = Range("B108:E126").Offset(0, j)
ActiveSheet.sort.SortFields.Clear
ActiveSheet.sort.SortFields.Add2 Key:=sortfluxcriteria _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.sort
.SetRange sortflux
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
j = j + 6
Loop
End Sub
First data range that needs to be sorted is B108:E126, sort on column E. Next range is H108:K126, sort on column k- offset by 6 columns from the first range
Sortfluxcriteria is the column I'm sorting and sortflux is the total range. The first iteration sorts fine but I can't seem to get the ranges defined correctly so that they move correctly to the second iteration. It completes with the first cycle and does nothing after that or just sorts on the original range twice. Any advice?
Here is my code thus far -
Sub Macro3()
Dim j As Integer
j = 0
Do While j < 12
Dim sortfluxcriteria As Range
Set sortfluxcriteria = Range("E108:E126").Offset(0, j)
Dim sortflux As Range
Set sortflux = Range("B108:E126").Offset(0, j)
ActiveSheet.sort.SortFields.Clear
ActiveSheet.sort.SortFields.Add2 Key:=sortfluxcriteria _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveSheet.sort
.SetRange sortflux
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
j = j + 6
Loop
End Sub