Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
391 views
in Technique[技术] by (71.8m points)

Excel VBA sort bug

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
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
  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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...