Descending Quicksort Issue

dawds

New Member
Joined
Apr 20, 2017
Messages
5
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,761
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
Joined
Dec 9, 2008
Messages
6,154
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
Joined
Apr 20, 2017
Messages
5
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,747
Messages
5,626,627
Members
416,195
Latest member
tonmcg

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top