Descending Quicksort Issue

dawds

New Member
Hi all,

I have a web scraper that pulls various sports teams' scores and puts each separate teams' scores into a different sheet. I've started scraping upwards of 200 teams a time, so I implemented the QuickSort algorithm as a means for sorting my sheets. The sort works perfectly when ascending, but I tried modifying the QuickSort code to sort descending and it stays as ascending. I've read multiple posts on the subject and they all modify the QuickSort code in the same fashion that I did, so I'm unsure where I'm going wrong. Any help would be greatly appreciated. Here's my code for creating my array to be sorted and then the QuickSort code I found online:

Rich (BB code):
``````Sub call_sort()
Dim myArray() As Variant

x = Worksheets.Count
ReDim myArray(5 To x)

For i = 5 To x
myArray(i) = Worksheets(i).Name
Next i

Application.Run "Personal.xlsb!quick_sort_asc", myArray, LBound(myArray), UBound(myArray)

For i = 5 To x
Worksheets(myArray(i)).Move Before:=Worksheets(i)
Next i

End Sub``````
Rich (BB code):
``````Public Sub quick_sort_asc(ByRef vArray As Variant, inLow As Long, inHi As Long)
Dim tmpLow As Long, tmpHi As Long
Dim pivot As Variant, tmpSwap As Variant

tmpLow = inLow
tmpHi = inHi
pivot = vArray((inLow + inHi) \ 2)

Do While (tmpLow <= tmpHi)
Do While (vArray(tmpLow) < pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Loop

Do While (pivot < vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Loop

If (tmpLow <= tmpHi) Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
Loop

If (inLow < tmpHi) Then quick_sort_asc vArray, inLow, tmpHi
If (tmpLow < inHi) Then quick_sort_asc vArray, tmpLow, inHi

End Sub

*** Lines modified for Descending ***
Do While (vArray(tmpLow) > pivot And tmpLow < inHi)
tmpLow = tmpLow + 1
Loop
Do While (pivot > vArray(tmpHi) And tmpHi > inLow)
tmpHi = tmpHi - 1
Loop``````

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Eric W

MrExcel MVP
I tried the quicksort code as written on an array with elements 0-4 and it worked. I changed the 2 lines that you marked to get it to sort descending, and it worked. I then used the same array with element numbers 5-9, and it did not work, ascending or descending. Possibly there's an issue with how it chooses the pivot points.

Try changing your array so that it starts at 0, or 1, and see what happens. Change your loops to:

Code:
``````For i = 0 to x - 5
myArray(i) = Worksheets(i + 5).Name
Next i``````

GTO

MrExcel MVP
Quickly tested, but appears to work:

Rich (BB code):
``````Public Sub quick_sort_desc(ByRef vArray As Variant, ByVal inLow As Long, ByVal inHi As Long)
' From shg's suggestion at: https://www.mrexcel.com/forum/excel-questions/550009-do-quicksort-descending-order.html#post2717838

Dim tmpLow As Long
Dim tmpHi As Long
Dim pivot As Variant
Dim tmpSwap As Variant

tmpLow = inLow
tmpHi = inHi
pivot = vArray((inLow + inHi) / 2)

Do
Do While vArray(tmpLow) > pivot
tmpLow = tmpLow + 1
Loop

Do While vArray(tmpHi) < pivot
tmpHi = tmpHi - 1
Loop

If tmpLow <= tmpHi Then
tmpSwap = vArray(tmpLow)
vArray(tmpLow) = vArray(tmpHi)
vArray(tmpHi) = tmpSwap
tmpLow = tmpLow + 1
tmpHi = tmpHi - 1
End If
Loop While tmpLow <= tmpHi

If inLow < tmpHi Then quick_sort_desc vArray, inLow, tmpHi
If tmpLow < inHi Then quick_sort_desc vArray, tmpLow, inHi

End Sub``````

Hope that helps,

Mark

dawds

New Member
Thanks for both responses. I realized a little while after I posted that I had accidentally put my scraper values in ascending order while testing the QuickSort, so no sorting ever occurred. I changed the starting value of the array as you suggested (both 1 and 0), but after myArray() values stayed the same even though it seems to run through the code fine. I shouldn't have to create a new array to store the sorted values correct? Thanks again

Replies
5
Views
133
Replies
5
Views
584
Replies
2
Views
143
Replies
3
Views
404
Replies
42
Views
2K

1,129,781
Messages
5,638,298
Members
417,020
Latest member
MSVII

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back