运行时错误1004.Range类的排序方法失败

Question

我编写了一个VBA宏，它将根据用户输入对行进行排序。因此，如果用户输入1，则排序将基于某个条件进行，如果输入2，则排序为另一个条件，依此类推。然而，当我运行代码时，我收到错误"run Time error 1004:Sort method of Range class failed"。任何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

Answer 1

当前区域拯救一天

当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

Answer 2

我在学习Excel VBA在线课程时遇到了同样的问题。很可能是同样的路线。错误出现在课程提供的电子表格中。我设法解决了这个问题，它与网上发现的这个问题有关。

https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/block-if-without-end-if?

所以这是一个更简单的修复方法，但后来我的电脑重新启动，我丢失了我编辑的宏，开始工作。

我尝试了VBasic2008"修复"，效果也很好。

只是我对我所经历的事情的评论，而不是试图说服或劝阻其他人。

Barry