VBA Code Problem 2

[Kenja824]

The below code is supposed to sort the excel table by the "R/L" column header. The problem I am running into is that I have a new file given to me and my code errors out when it gets to this bit of code. I believe it is because this excel file happens to not have a "R/L" column.

How would I change this so that if the column is not found, it will skip this bit of code and continue to the next?


ActiveWorkbook.ActiveSheet.ListObjects("Table1") _
.Sort.SortFields.Clear
ActiveWorkbook.ActiveSheet.ListObjects("Table1") _
.Sort.SortFields.Add Key:=Range("Table1[[#All],[R/L]]"), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.ActiveSheet.ListObjects( _
"Table1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

 

[IDS]

The simplest way is to insert:
On Error Resume Next

before the sort code.

To make it more robust insert after End With:
On Error Goto Errorhandlingcode:

Errorhandlingcode is a line label, followed by code to handle errors.



Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[Kenja824]

Thanks Doug

I tried the Resume Next and it worked great with that. Sometime when I have more time to spend, I will dig into the Errorhandling stuff. )

 

[SkipVought]

Here's another approach using the Find method...

'
    If Not ActiveSheet.Range("Table1[#Headers]").Find("R/L") Is Nothing Then
        With ActiveSheet.ListObjects("Table1")
            .Sort.SortFields.Clear
            .Sort.SortFields.Add _
                Key:=ActiveSheet.Range("Table1[[#All],[R/L]]"), _
                SortOn:=xlSortOnValues, _
                Order:=xlAscending, _
                DataOption:=xlSortNormal
            With .Sort
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End If

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}