×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VBA Code Problem
2

VBA Code Problem

VBA Code Problem

(OP)
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

RE: VBA Code Problem

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/

RE: VBA Code Problem

(OP)
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. clown

RE: VBA Code Problem

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

CODE

'
    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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources