troubleshooting Question

Scripting for Excel VBA 5 level sort

Avatar of dkcoop03
dkcoop03Flag for United States of America asked on
Microsoft ExcelMicrosoft Applications
11 Comments1 Solution354 ViewsLast Modified:
I have part of a complex sort done.  I'm attaching the spreadsheet and code so far.  I basically need a 5 level sort.  Categories with a minus "-" in front (col A) of them expand to contain sub categories -- they can also have a plus "+" in front of them indicating that they are rolled up.  If there is a space " " in column A that indicates a category with no subcategories.  I need to sort the sub categories within the categories and then sort every category against other categories.  I have that part done.  The complexity comes because there can be sub sub categories, or another level of category within the sub category.  In that case I want those categories sorted within the sub category.  So I somehow need to test both column A and column B for the +, - or space.  So I'm wondering first, is my code the best way to do this (It does work for sub categories and categories) and second, how to incorporate the testing for sub sub categories.  I'm using Excel 2010.  Thanks
Private Sub cmdSort_Click()
Dim r As Range
Dim s As Range
Dim sign As Range

Dim lastrow As Integer
Dim catcount As Double
Dim catcount2 As Double

lastrow = Range("E3").End(xlDown).Row
catcount = lastrow

Set r = Range("G3")
Set s = Range("H3")
Set sign = Range("A3")
Set subsign = Range("B3")

While r.Row <= lastrow
    If sign.Value <> "" Then
        s.Formula = catcount + 0.1
        r.Formula = r.Offset(0, -2).text
        catcount2 = catcount
        catcount = catcount - 1
    Else   'populate cell with value in cell above
        r.Formula = r.Offset(-1, 0).text
        s.Formula = catcount2
    End If
    'drop down one row
    Set s = s.Offset(1, 0)
    Set r = r.Offset(1, 0)
    Set sign = sign.Offset(1, 0)
Wend

Call FourKeySort

End Sub

Sub FourKeySort()

    SortAll "H3"
    SortAll "G3"
    SortAll "J3"
    SortAll "I3"
    SortAll "E3"

End Sub

Sub SortAll(x As String)

    Range(x).Sort _
        Key1:=Range(x), _
        Order1:=xlDescending, _
        Header:=xlNo
End Sub

Open in new window

testsort.xlsx
ASKER CERTIFIED SOLUTION
gyetton

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros