Problem With VBA Sort Code...

[ByerRA]

I've been beating my head agianst the wall for two hours on this...

I can not get the following little piece of code to run properly, I keep getting a "Application-defined or object-defined error" when it adds the first sort key.

I've even recorded the macro directory from Excel and still get the error, I've recorded the sort in Office 2003 and still get the same error. I defined the sort range before the sort, I've tried just about everthing I could find though Google..

No matter what I do, I can't get this to work.

Any and all help would be appreciated as it's driving me nuts!!!

Public Sub Test_Format(File_Name As String)
 
Dim xls As Excel.Application
Dim Compare_Workbook As Excel.Workbook
Dim Number_Rows As Long
 
    On Error GoTo Format_ERROR
    Set xls = GetObject(, Excel.Application)
    If xls.Application.DisplayAlerts Then
        xls.Application.DisplayAlerts = False
    End If
    Set Compare_Workbook = xls.Workbooks.Open(File_Name)
    Do Until Compare_Workbook.Sheets.Count = 4
        Compare_Workbook.Sheets(1).Copy _
            After:=Compare_Workbook.Sheets(1)
    Loop
 
    Number_Rows = CLng(Compare_Workbook.Worksheets(Sheet_Count).UsedRange.Rows.Count)
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields.Clear
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields. _
          Add Key:=Range("$L$2:$L$" & CStr(Number_Rows)), _
          SortOn:=xlSortOnValues, Order:=xlAscending, _
          DataOption:=xlSortNormal
    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields. _
          Add Key:=Range("$Y$2:$Y$" & CStr(Number_Rows)), _
          SortOn:=xlSortOnValues, Order:=xlDescending, _
          DataOption:=xlSortNormal
    With Compare_Workbook.Worksheets(Sheet_Count).Sort
         .SetRange Range("$A$1:$AJ$" & CStr(Number_Rows))
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
     End With
    Compare_Workbook.Close SaveChanges:=True
    xls.Application.Quit
    If Not Compare_Workbook Is Nothing Then
        Set Compare_Workbook = Nothing
    End If
    If Not xls Is Nothing Then
        Set xls = Nothing
    End If
    Exit Sub
 
 
Format_ERROR:
    If Err = 429 Then
        Err.Clear
        Set xls = CreateObject("Excel.Application")
        Resume Next
    End If
    If Not Compare_Workbook Is Nothing Then
        Compare_Workbook.Close SaveChanges:=False
        Set Compare_Workbook = Nothing
    End If
    If xls.Application.DisplayAlerts Then
        xls.Application.DisplayAlerts = False
    End If
    xls.Application.Quit
    If Not xls Is Nothing Then
        Set xls = Nothing
    End If
    Exit Sub
 
End Sub

 

[ByerRA]

Are you sure that Number_Rows is getting the right value?

Yes, "Number_Rows" is getting the proper value (in the case of my test data there's 54327 rows which is what "Number_Rows" is)

Also why do you have all the conversion functions like CLng and CStr?

Ahh, the reason for the conversion functions is that some time back in Office 2003 we had all kinds of "mysterious" problems which we eventually tracked down to type conversions so we made sure in all our code that things get forced to what they should be.

It's a force of habit I've had ever since and I've been working on updating all our code to remove the unecessary ones.

Now, here's where it's strange again.

All of a sudden, out of the blue yesterday the above code started working AS-IS on it's own. I've been testing it for two days and not a problem.

NOW, having said that....

I copied the EXACT code into another module and another subroutine (module called "Reports", subroutine called "InvoiceSummary") changing the workbook references (workbook object named "InvoiceSummary_Workbook") and now I'm having the EXACT SAME problem/error in the EXACT SAME line (setting the first sort key) with the copied code that I had with the previous code and the previous code is still running perfect!

This is driving me nuts!!!

 

[Norie]

Using conversion functions is fine but they aren't always needed and can in fact cause problems.

VBA will sometimes automatically convert the values to the correct type for you.

 

[ByerRA]

Using conversion functions is fine but they aren't always needed and can in fact cause problems.

VBA will sometimes automatically convert the values to the correct type for you.

I know that if I remove the CStr from around the "Number_Rows" in the ranges I will get a "Application-defined or object-defined error" error.

(I just tested this and yep, still the case, have to have the CStr around "Number_Rows" in the ranges)

I removed the CLng's and it made no difference.

 

[Norie]

Well I tested the same thing and never got an error.

Dim rng As Range
Dim Number_Rows As Long
 
Number_Rows = 100
 
Set rng = Range("$L$2:$L$" & Number_Rows)
 
MsgBox rng.Address

Isn't that error the one you described right at the start of this thread?

 

[ByerRA]

Well I tested the same thing and never got an error.

Dim rng As Range
Dim Number_Rows As Long
 
Number_Rows = 100
 
Set rng = Range("$L$2:$L$" & Number_Rows)
 
MsgBox rng.Address

Isn't that error the one you described right at the start of this thread?

Not quite, the problem is a "Application-defined or object-defined error" when I set the first key in the sort.

Specifically the line...

    Compare_Workbook.Worksheets(Sheet_Count).Sort.SortFields. _
          Add Key:=Range("$L$2:$L$" & CStr(Number_Rows)), _
          SortOn:=xlSortOnValues, Order:=xlAscending, _
          DataOption:=xlSortNormal

I can set ranges all day long with no problems, it's just that for SOME reason the above code is unstable, and right now it's working perfect in one section of code, but fails in another part.

 

[Norie]

I keep getting a "Application-defined or object-defined error" when it adds the first sort key.

I will get a "Application-defined or object-defined error" error.

Am I missing something but aren't those the same error?

Which would suggest removing CStr is nothing to do with things.

By the way I've just recalled something I meant to ask you right at the start - where are you running this code?

In Excel? Or are you automating Excel from another application?

 

[MrBillBenson]

Hey I don't know if anyone is still following this thread a year later but I will jump in and hope for the best. I am getting the exact same error, and in fact I reduced my code to remove the SortOn argument, and then it worked. Maybe cuz I had nothing but numbers in my key field, the option was (a) irrelevant and (b) potentially(??) problematic.

I see no reason for it, but it's true. These two lines of code worked:

Cells(1, 1).CurrentRegion.Sort Key1:=w.Cells(1, 1), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


and

Cells(1, 1).CurrentRegion.Sort

but

Cells(1, 1).CurrentRegion.Sort Key1:=w.Cells(1, 1), SortOn:=xlSortOnValues, Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

failed and even

Cells(1, 1).CurrentRegion.Sort Key1:=w.Cells(1, 1), SortOn:=xlSortOnValues

failed.

Go figure...

Now, that is not gonna help me when I need the argument! In fact, whether or not I need it is situationally dependent.

Somehow I cannot believe the answer stops here...

 

[shg]

You're mixing methods.

SortOn is an Excel 2007+ argument. The rest of your arguments are for Excel 2003-, which will work in 2007, but you need to pick one or the other.

 

[MrBillBenson]

Thanks much!

 

[kpark91]

You need to record on Excel 2003 sort methods again.
The code you have is not executable in Excel 2003 but in Excel 2007+ just like shg has mentioned.

here is the code for sorting in Excel 2003 which I have pulled up from an old post.

    newWS1.Select
    Selection.Sort Key1:=Range("E1"), Order1:=xlAscending, Key2:=Range("D1") _
    , Order2:=xlAscending, Key3:=Range("A1"), Order3:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
    xlSortNormal

It sorts the columns by E1, D1 then A1.