时间戳

首页 > 解决方案 > 运行时错误 1004。 Range 类的排序方法失败

excel - 运行时错误 1004。 Range 类的排序方法失败

问题描述

我编写了一个 VBA 宏，它将根据用户输入对行进行排序。因此，如果用户输入 1，则排序将基于某个条件进行，如果输入 2，则将根据另一个条件进行排序，依此类推。但是，当我运行代码时，我收到错误“运行时错误 1004：Range 类的排序方法失败”。任何 VBA 专家都可以帮助我如何克服这个错误。下面是整个代码块：

Public Sub Sortlist()

Dim userinput As String
Dim tryagain As Integer

userinput = InputBox("1 = Sort By Division,2 = Sort by Category, 3 = Sort by Total sales")
If userinput = "1" Then
DivisionSort
ElseIf userinput = "2" Then
CategorySort
ElseIf userinput = "3" Then
TotalSort
Else
tryagain = MsgBox("Incorrect Value.Try again?", vbYesNo)
If tryagain = 6 Then
Sortlist
End If
End If

End Sub
------------------------------------
Sub DivisionSort()
'
' Sort List by Division Ascending
'

'
    Selection.Sort Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub
----------------------------------------------
Sub CategorySort()
'
' Sort List by Category Ascending
'

'
    Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub
--------------------------------
Sub TotalSort()
'
' Sort List by Total Sales Ascending
'

'
    Selection.Sort Key1:=Range("F4"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

标签： excelvbasorting

解决方案

CurrentRegion 拯救世界

当您Selection超出范围时，您的代码失败。因此，我创建了一个Sub带有一个名为的参数SortRange，该参数用于CurrentRegion始终“指向”该范围。

Option Explicit

Public Sub Sortlist()

    Dim userinput As String
    Dim tryagain As Integer

    userinput = InputBox("1 = Sort By Division,2 = Sort by Category, 3 = Sort by Total sales")

    If userinput = "1" Then
        DivisionSort
    ElseIf userinput = "2" Then
        CategorySort
    ElseIf userinput = "3" Then
        TotalSort
    Else
        tryagain = MsgBox("Incorrect Value.Try again?", vbYesNo)
        If tryagain = 6 Then
            Sortlist
        End If
    End If

End Sub
'------------------------------------
Sub SortRange(rng As Range)
        rng.CurrentRegion.Sort Key1:=rng, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub
'------------------------------------
Sub DivisionSort()
'
' Sort List by Division Ascending
'
    SortRange Range("A4")

End Sub
'----------------------------------------------
Sub CategorySort()
'
' Sort List by Category Ascending
'
    SortRange Range("B4")

End Sub
'--------------------------------
Sub TotalSort()
'
' Sort List by Total Sales Ascending
'
    SortRange Range("F4")

End Sub

推荐阅读