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
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
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
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.
RE: VBA Code Problem
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 IfSkip,
Just traded in my OLD subtlety...
for a NUance!