vba 在vba中按字母顺序自动排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23073372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
automatically sort alphabetically in vba
提问by Doolie1106
I want to sort info on my sheet according to their last name (which is column 1). there's a form controlbutton on the sheet that filters the info in accordance to the name of the button. everytime each of these form controlbuttons are clicked, i want to sort the info alphabetically. so i've used the recordon macro to see what excel does to get familiar with it but i'm quite stuck... the problem is the rand countAit gives me an error of type mismatch
我想根据他们的姓氏（第 1 列）对我的工作表上的信息进行排序。工作表上有一个form control按钮，可根据按钮名称过滤信息。每次form control点击这些按钮时，我都想按字母顺序对信息进行排序。所以我用record宏观，看看有什么Excel不会来熟悉它，但我很卡...问题是r和countA它给我的错误type mismatch
in total i have 17 columns (A to Q - but i don't want to really set a range to the columns, just in case i add more columns later) that contains information related to the last namewhich is column 1 and starts from row 3
我总共有 17 列（A 到 Q - 但我不想真正为列设置范围，以防我稍后添加更多列），其中包含与第last name1 列相关的信息，并从第 3 行开始
Sub btnPrinceRupert()
Dim ws As Worksheet
Dim r As Range
Set r = ws.Cells(2, 1)
Set ws = Worksheets("Master")
Call filterMyTable(xPrinceRupert)
Call changeTitle("PrinceRupert")
r.Select
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=r, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.sort
.SetRange (WorksheetFunction.CountA(Range("A:A")))
'essentially, i want the range to be up to the last entry of the table
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
回答by Taotao
For the first issue, you need to set the ws before set the r value; and the Second issue, the result of WorksheetFunction.CountA(Range("A:A")) is a number, not Range. so the code should write like this:
第一个问题，需要先设置ws，再设置r值；第二个问题，WorksheetFunction.CountA(Range("A:A")) 的结果是一个数字，而不是范围。所以代码应该这样写：
Sub btnPrinceRupert()
Dim ws As Worksheet
Dim r As Range
Set ws = Worksheets("Master")
Set r = ws.Cells(2, 1)
Call filterMyTable(xPrinceRupert)
Call changeTitle("PrinceRupert")
r.Select
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=r, _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ws.sort
.SetRange (Range("1:" & WorksheetFunction.CountA(Range("A:A")))
'essentially, i want the range to be up to the last entry of the table
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
