MeisterConrad
New Member
- Joined
- Jan 17, 2017
- Messages
- 39
- Office Version
- 2007
The last time I posted code, someone told me I was posting it incorrectly. I hope I have posted it correctly this time. If I'm still doing it wrong, please forgive me, and tell me how to do it right.
I have the following code for sorting table data, but something's wrong with the way I'm referencing the sort keys.
As there are several tables on the sheet, the first bit identifies the table to be sorted.
The third bit is the code for the data-sort operation.
The middle chunk is where I'm guessing my problem is. I think my syntax in setting rng1 and rng2 is faulty. Can anybody set me straight?
And also, if there is a simpler/more efficient/elegant way of doing this, I'm all for suggestions.
Thanx!
I have the following code for sorting table data, but something's wrong with the way I'm referencing the sort keys.
As there are several tables on the sheet, the first bit identifies the table to be sorted.
The third bit is the code for the data-sort operation.
The middle chunk is where I'm guessing my problem is. I think my syntax in setting rng1 and rng2 is faulty. Can anybody set me straight?
And also, if there is a simpler/more efficient/elegant way of doing this, I'm all for suggestions.
Thanx!
VBA Code:
Dim LedgerNumber As String
Dim X As Integer
If InStr(ActiveCell.Text, "Table") > 0 Then
LedgerNumber = Mid(ActiveCell, 6, 2)
X = CInt(LedgerNumber)
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Set ws = ActiveSheet
Set tbl = ws.ListObjects("Table" & X)
Set rng1 = Range("Table" & X).ListColumns(2)
Set rng2 = Range("Table" & X).ListColumns(3)
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=rng1, Order:=xlAscending
.SortFields.Add Key:=rng2, Order:=xlAscending
.Header = xlYes
.Apply
End With
End If