Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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)

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, _
End Sub
Avatar of gyetton
gyettonFlag of United Kingdom of Great Britain and Northern Ireland image

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

This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answers