Better Ideas - Find order of subcategories

[JPARKHURST]

I'm going to post and go home, so no hurry on this.

I have a list of jobs, and each has a category. Of those categories, one has 4 subcategories, but they are not organized in a parent/child relationship, they are only distinguished by their category.

SOO, I am wanting to sort my job list, according to categories and then date. This works great. Next, I want to sort the 4 sub categories independently of the other jobs.

If this was one category, I would find the first instance of it's name, then go about my bidness. However, i am wanting to 'find' if it is one of four, and that' where I'm stuck.

I have been finding and defining the first cell of each instance. And here I'm stuck. I pulled the row value to compare, but as there are 4 categories, it's nto a simple "IF" statement chain.

The best solution I can currently think of is to setup a [massive] switch statement, but that sounds sloppy. Or I could loop through the range or array, set a boolean when the first instance of a category is found, and at the time I find a new one, compare and sort values (which is winning right now in my tired brain)

NOTE: Eventually I would much prefer to take this list, convert it to an array, and then push that array back out to the page. It seems it may be more simple to do my sorting here, but I'm not familiar with arrays in here yet, and so have just gone the range route for now.

Other notes:
yes, ranges are dynamic,will always change
No, i can't easily modify the data, it's a dump, and I don't want to mess with that aspect of it (at the moment). I can setup an additional worksheet, but I'm getting a little large for my liking as is.

I'm really just looking for logic and prefer to find the specifics on my own. That being said, I'm not too proud for rote learning. I don't think the code will matter, but if it does, here it is:

Public Sub OrgJD(ws517 As Worksheet)
    'On Error Resume Next
    
    Dim icount As Integer
    Dim lfr517 As Long, llr517 As Long, lv517 As Long, lrc As Long, lrd As Long, lre As Long, lrf As Long
    Dim r517a As Range, r517b As Range, r517c As Range, r517d As Range, r517e As Range, r517f As Range
    
    
    'Application.ScreenUpdating = False
    
    llr517 = ws517.Cells(Rows.Count, "A").End(xlUp).Row
    
    Set r517a = ws517.Range("A2:A" & llr517)
    
    r517a.Replace what:="Assy", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=False
        
    r517a.Replace what:="Pack", Replacement:="", _
    SearchOrder:=xlByColumns, MatchCase:=False
    
    'Set r517a = Nothing
    Set r517a = ws517.Range("A2:S" & llr517)
    'MsgBox ("r517a is set: " & r517a.Address)
    
    'sort rows based on workstation, then date
    r517a.Sort key1:=Range("A2:A" & llr517), order1:=xlAscending, _
    key2:=Range("Q2:Q" & llr517), order2:=xlAscending
    
    'msgbox(Sort has been attempted - did it work?)
    Set r517b = Range("A2:A" & llr517)
    MsgBox ("I am sending " & r517b.Address & " to FindValue")
    Set r517c = FindValue(wsJobDispatch, r517b, "AMS Cab")
    lrc = r517c.Row
    
    Set r517d = FindValue(wsJobDispatch, r517b, "AMS Chest")
    lrd = r517d.Row
    
    Set r517e = FindValue(wsJobDispatch, r517b, "AMS TC")
    lre = r517e.Row
    
    Set r517f = FindValue(wsJobDispatch, r517b, "AMS WS")
    lrf = r517f.Row
    
    
    If (lrc < lrd) Then
    
    
    'STOPPING HERE
    'IDEAS:
    'YOU CAN IF STATEMENTS TO ATTEMPT TO PROVIDE AN ORDER, OR A SELECT/CASE STATEMENT
    'POSSIBLY LOOP THROUGH THE RANGE, ADDING IN ORDER THE FIRST OCCURANCE, WITH A BOOLEAN TO SEE IF PLACE 1-4 WAS FOUND YET
    'POSSIBLY USE AN ARRAY TO SORT THEM OUT

This my referenced FindValue sub

Public Function FindValue(r901 As Range, s901 As String) As Range
Dim r902 As Range
    
    With r901
        Set r902 = .Find(s901, LookIn:=xlValues)
        If Not r901 Is Nothing Then
            Application.Goto r901, True
        Else
            MsgBox ("No good, Pappi")
        End If
    End With
    
    Set FindValue = r902
End Function

Thanks for any suggestions. Feel free to point out any mistakes or optimizations I can look at. It's all much appreciated.

Jon

 

[JPARKHURST]

As with most things, the problem was in the approach, not the code.

Just in case anybody has a similar issue, in this case the solution was to stop lookking at the categories, sorting, etc. Instead I know the categories which come before and after the sub-groups I was wanting to search. I simply set my range to last row+1 and first row-1 and capture the 'shadow' of the range.

HTH anybody else,

Jon