Sorting Code (alphabetize) stopped working

[sassriverrat]

This line of code used to work. Now this, among a few others, doesn't seem to work. It kicks up a 438 "Object doesn't support this property or method" code and highlights the top line.... ideas? I can sort if I manually highlight and click sort....

    Range("B3:K102").Select
    ActiveWorkbook.Worksheets("Ports").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ports").Sort.SortFields.Add2 Key:=Range("B3:B102") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ports").Sort
        .SetRange Range("B3:K102")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[Yongle]

It kicks up a 438 "Object doesn't support this property or method" code and highlights the top line

Sub ThisLineIsGood()
   Range("B3:K102").Select
End Sub

The above line works without issue for me but what is happening in your code before that line ?

 

[sassriverrat]

Sub ThisLineIsGood()
   Range("B3:K102").Select
End Sub

The above line works without issue for me but what is happening in your code before that line ?

unfortunately nothing. I meant to post the full sub and missed it. Here ya go

Sub Alphabetize()

'Begins Error Handling Code
On Error GoTo Helper


Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False
    Range("B3:K102").Select
    ActiveWorkbook.Worksheets("Ports").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ports").Sort.SortFields.Add2 Key:=Range("B3:B102") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Ports").Sort
        .SetRange Range("B3:K102")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.EnableEvents = False


'Error Clearing Code
Exit Sub
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1129] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Sub
        ElseIf resp = vbCancel Then
            Exit Sub
        End If
        
End Sub

[/code]

 

[Yongle]

Range B3:K102 is referred to in the code but selected cells are not used so the line is of no consequence
Delete the line below

Range("B3:K102").Select

and run the code again to see if the code now stops further down

 

[sassriverrat]

Yep- kicked a 438 Object doesn't support this property or method
on this line of code

    ActiveWorkbook.Worksheets("Ports").Sort.SortFields.Add2 Key:=Range("B3:B102") _        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Yongle]

Sub SortDataWithoutHeader()
       Dim rng As Range
       Set rng = ActiveWorkbook.Worksheets("Ports").Range("B3:K102")
       rng.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo
End Sub

Also - spot your error!

Sub Alphabetize()
On Error GoTo Helper
Application.DisplayAlerts = [COLOR=#ff0000]False[/COLOR]
Application.ScreenUpdating = [COLOR=#ff0000]False[/COLOR]
Application.EnableEvents = [COLOR=#ff0000]False[/COLOR]
[I]
Sorting code here [/I]

Application.DisplayAlerts = False ??
Application.ScreenUpdating = False ?? 
Application.EnableEvents = False ??

 

[sassriverrat]

Ahhhh

Sloppy on my part. That's fixed. Thank you.


As for your code- it kicked an error again- 1004 Sort method of range class failed....

Not sure why these are kicking errors because my original code worked at one time....

 

[Yongle]

Is the worksheet protected

If so ...

Sub SortDataWithoutHeader()
    Const pWord = "[COLOR=#ff0000][I]password[/I][/COLOR]"
    Dim rng As Range
    Set rng = ActiveWorkbook.Worksheets("Ports").Range("B3:K102")
    With rng
        .Parent.Unprotect pWord
        .Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo
        .Parent.Protect pWord
    End With
End Sub

 

[sassriverrat]

Ahhh ha! Yes

So that's very interesting. Yes, the sheet was password protected. However, the fields being sorts were not part of the protection so I figured they should not have a problem....obviously not the case.

Thank you!

Now I get to ask why my buttons aren't working correctly on another routine!

 

[Yongle]

Glad that helped

Now I get to ask why my buttons aren't working correctly on another routine!

You do - but on another thread please