Excel VBA排序错误 [英] Excel VBA sort bug

问题描述

在过去的几周里(我成功地)，我一直在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

推荐答案

1. 密钥:= source.Range(source.Cells(rowStart，startNames)... -您此处不应具有 source.Range -您的密钥是单个单元格 source.Cells(rowStart，startNames).
2. 作为建议-将 ... SortFields.Add2 更改为

1. Key:=source.Range(source.Cells(rowStart, startNames)... - You shouldn't have source.Range here - your key is the single cell source.Cells(rowStart, startNames).
2. As a recommendation - change ...SortFields.Add2 to ...SortFields.Add. The ..Add2 will definitely not work in older versions of Excel.

这篇关于Excel VBA排序错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！