VBA sorting column by name in first row

[drangel]

Hey all-

I am attempting to "Sort" data based on the label/heading in the first row (i.e. Account, then by Type).

[TABLE="width: 988"]
<tbody>[TR]
[TD][TABLE="width: 1081"]
<tbody>[TR]
[TD]Type[/TD]
[TD]Date[/TD]
[TD]Num[/TD]
[TD]Name[/TD]
[TD]Memo[/TD]
[TD]Account[/TD]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]0/16/2014[/TD]
[TD]111111[/TD]
[TD]John[/TD]
[TD]John company[/TD]
[TD]10000 · A/R - xx[/TD]
[TD][/TD]
[TD="align: right"]150.00[/TD]
[TD="align: right"]124,780.23[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the code when I recorded the macro:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E2:F=E100000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A100000" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:Z100000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The problem is that those columns are not always in the same column order when I generate the data. The macro Sorts by column letter (i.e. column F and A) instead of by the column name. Does anyone know how to Sort by finding the labels in the first column and Sorting by that column despite the fact that the column may be in a different location on the spreadsheet.

Also, if I generate a report and it does not contain that name title (i.e. Account), then to do nothing and not sort and move to the next Sort column (i.e. Type). Is that even possible?

... Later in the code, I use the Subtotal feature to sum the Debit and Credit columns (another spot I have to find the column letter/number again) at every change in Account (column E or 6th column) number, and then Subtotal again by a change in Type (column A or column 1).

My code looks like the following:

Selection.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(11, 12), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(11, 12), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True



Am what I trying to do feasible???

Thank you in advance for your input!

 

[JLGWhiz]

Sub SortStuff()
Dim sh As Worksheet, cl1 As Long, cl2 As Long, lr As Long, fn1 As Range, fn2 As Range
'Other code here if required
Set sh = Sheets("Sheet1") 'Edit sheet name
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set fn1 = sh.Rows(1).Find("Account", , xlValues, xlPart)
    If Not fn1 Is Nothing Then
        cl1 = fn1.Column
    End If
Set fn2 = sh.Rows(1).Find("Type", , xlValues, xlPart)
    If Not fn2 Is Nothing Then
        cl2 = fn2.Column
    End If
If Not cl1 Is Nothing Then
    sh.Range("A1:Z" & lr).Sort sh.Cells(1, cl1), xlAscending, sh.Cells(1, cl2), xlAscending
Else
    sh.Range("A1:Z" & lr).Sort sh.Cells(1, cl2), xlAscenting
End If
'Other code here if required
End Sub

You can try this to see if it does your sort like you want. If it does, then you can use the cl1 and cl2 variables anywhere in the subsequent lines of code as a column number in the Cells(r, c) format, ie. Cells(1, cl1) would be row 1 and the column where 'Account' resides. The code is written so that it will sort only on 'Type' column if 'Account' is not found. But if you use cl1 variable later in the code when it was not found, you will get an error if it is not used within a conditional statement.

 

[drangel]

JLGWhiz-

Thank you very much! However, when I attempt to run the Sub SortStuff(), a "Compile Error: Type Mismatch" pops up and am not able to run/test it. Do you have any suggestions as how to get around to testing it? I have attempted to run it in my Personal as well as inserting Module/copying and pasting code into module for the worksheet and the same error message pops up...

Quick question. Is the "lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row" finding the last row (used as a better replacement for me having prepopulated to Z100000)?

 

[JLGWhiz]

When you get the error message and click the debug button, which line of code is highlighted?

Quick question. Is the "lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row" finding the last row (used as a better replacement for me having prepopulated to Z100000)?

Yes, it allows the vertical range to adjust based on additions and deletions.

 

[drangel]

The very first line- Sub SortStuff()

 

[JLGWhiz]

Think I figured out the problem. Had a typo in there. Try this version.

Sub SortStuff2()
Dim sh As Worksheet, cl1 As Long, cl2 As Long, lr As Long, fn1 As Range, fn2 As Range
'Other code here if required
Set sh = Sheets("Sheet1") 'Edit sheet name
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set fn1 = sh.Rows(1).Find("Account", , xlValues, xlPart)
    If Not fn1 Is Nothing Then
        cl1 = fn1.Column
    End If
Set fn2 = sh.Rows(1).Find("Type", , xlValues, xlPart)
    If Not fn2 Is Nothing Then
        cl2 = fn2.Column
    End If
If Not fn1 Is Nothing Then
    sh.Range("A1:H" & lr).Sort Key1:=sh.Cells(1, cl1), Order1:=xlAscending, Key2:=sh.Cells(1, cl2), Order2:=xlAscending, Header:=xlYes
Else
    sh.Range("A1:H" & lr).Sort Key1:=sh.Cells(1, cl2), Order1:=xlAscenting, Header:=xlYes
End If
'Other code here if required
End Sub

 

[drangel]

I attempted the new one but a "run=time error "1004": Application-defined or object-defined error" populates the screen on the bottom of code on the following line:

Else
sh.Range("A1:H" & lr).Sort Key1:=sh.Cells(1, cl2), Order1:=xlAscenting, Header:=xlYes


I fixed the spelling of the word in red (changed "t" to "d") but the message still pops up... Any ideas as to what it might be?

Thank you again for your time!

 

[JLGWhiz]

I attempted the new one but a "run=time error "1004": Application-defined or object-defined error" populates the screen on the bottom of code on the following line:

Else
sh.Range("A1:H" & lr).Sort Key1:=sh.Cells(1, cl2), Order1:=xlAscenting, Header:=xlYes
I fixed the spelling of the word in red (changed "t" to "d") but the message still pops up... Any ideas as to what it might be?

Thank you again for your time!

Yep, 'Ascenting' just won't work. I also forgot to change the sort range back after testing. Hopefully it is now all fixed.

Sub SortStuff3()
Dim sh As Worksheet, cl1 As Long, cl2 As Long, lr As Long, fn1 As Range, fn2 As Range
'Other code here if required
Set sh = Sheets("Sheet1") 'Edit sheet name
lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
Set fn1 = sh.Rows(1).Find("Account", , xlValues, xlPart)
    If Not fn1 Is Nothing Then
        cl1 = fn1.Column
    End If
Set fn2 = sh.Rows(1).Find("Type", , xlValues, xlPart)
    If Not fn2 Is Nothing Then
        cl2 = fn2.Column
    End If
If Not fn1 Is Nothing Then
    sh.Range("A1:Z" & lr).Sort Key1:=sh.Cells(1, cl1), Order1:=xlAscending, Key2:=sh.Cells(1, cl2), Order2:=xlAscending, Header:=xlYes
Else
    sh.Range("A1:Z" & lr).Sort Key1:=sh.Cells(1, cl2), Order1:=xlAscending, Header:=xlYes
End If
'Other code here if required
End Sub