本文介绍了Excel VBA排序错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在过去的几周里(我成功地),我一直在Excel VBA中编写宏,并且出现了错误.这是我认为存在该错误的代码(通常会产生此错误:运行时错误'1004':应用程序定义的错误或对象定义的错误").
I've been writing macros in Excel VBA for the last couple weeks (mostly successfully) for my internship and have come to bug. Here's the code that I believe has the bug (which usually generates this error: "Run-time error '1004': application-defined or object-defined error").
我希望我可以显示正在使用的数据以提供更多背景信息,但这是机密的员工数据.您需要知道的是,此代码的目标是按字母顺序对员工姓名进行排序.
I wish I could show the data I'm working with to give more context but it's confidential employee data. What you need to know is that the goal of this code is to alphabetically sort employee names.
'Establish where names are contained in data
startLetter = InputBox("Please enter the letter of the first column that contains the employee names:", "Where Names Start")
endLetter = InputBox("Please enter the letter of the last column that contains the employee names:", "Where Names End")
startNames = source.Range(startLetter & 1).Column
endNames = source.Range(endLetter & 1).Column
source.Range(source.Cells(rowStart, startNames), source.Cells(rowEnd, endNames)).Select
source.Sort.SortFields.Clear
source.Sort.SortFields.Add2 Key:=source.Range(source.Cells(rowStart, startNames) _ <---error line
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With source.Sort
.SetRange source.Range(source.Cells(rowStart, startNames), source.Cells(rowEnd, endNames))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
推荐答案
-
密钥:= source.Range(source.Cells(rowStart,startNames)...
-您此处不应具有source.Range
-您的密钥是单个单元格source.Cells(rowStart,startNames)
. - 作为建议-将
... SortFields.Add2
更改为... SortFields.Add
... Add2
在早期版本的Excel中绝对无法使用.
Key:=source.Range(source.Cells(rowStart, startNames)...
- You shouldn't havesource.Range
here - your key is the single cellsource.Cells(rowStart, startNames)
.- As a recommendation - change
...SortFields.Add2
to...SortFields.Add
. The..Add2
will definitely not work in older versions of Excel.
这篇关于Excel VBA排序错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!