Excel VBA：排序，然后复制和粘贴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6117339/
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
Excel VBA: Sort, then Copy and Paste
提问by Steve T.
All, I need to write a macro that does the following:
所有，我需要编写一个执行以下操作的宏：
On entry data into the last blank cell in column E, sort the entire worksheet by column E in descending order
Once the worksheet is sorted:
2a. Copy the cell to the adjacent cell immediately to the leftof the cell into which the data was first entered
2b. Paste the copied data into the first column of the same row from which the data was originally entered
2c. Move the cursor to the adjacent cell immediately to the rightof the cell into which the data was first entered
在 E 列的最后一个空白单元格中输入数据时，按 E 列降序对整个工作表进行排序
工作表排序后：
2a. 将单元格复制到第一次输入数据的单元格左侧的相邻单元格
2b. 将复制的数据粘贴到最初输入数据的同一行的第一列
2c。将光标移动到第一次输入数据的单元格右侧的相邻单元格
Below, I show the sort on entry code, which works. However, I cannot thenget the code to copy, paste, and move correct. My most common problem: after data entry, the rows move, but the cursor stays in the row where the data was first entered. Can anyone help? (I can't even get the indenting right on this post!)
下面，我展示了对入口代码的排序，这是有效的。但是，我不能再让代码复制，粘贴和移动正确。我最常见的问题：数据输入后，行移动，但光标停留在第一次输入数据的行中。任何人都可以帮忙吗？（我什至无法在这篇文章中正确缩进！）
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets("Sheet1").Range("E:E"), Target) Is Nothing) Then
DoSort
End If
End Sub
Private Sub DoSort()
Worksheets("Sheet1").Range("A:E").Sort Key1:=Worksheets("Sheet1").Range("E1"), Order1:=xlDescending, Header:=xlYes
End Sub
回答by Jean-Fran?ois Corbett
Regarding 1, 2a, and 2b: It's more straightforward to do the copying before sorting. That way, the copied value will be sorted along with the rest.
关于 1、2a 和 2b：在排序之前进行复制更直接。这样，复制的值将与其余值一起排序。
Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Worksheets("Sheet1").Range("E:E"), Target) _
Is Nothing) Then
' First copy
Target.Offset(0, -1).Copy Destination:=Target.Offset(0, -4)
' Then sort
DoSort
End If
End Sub
This leaves the question (2c) of how to move the active cell to the appropriate row after the rows have been sorted. Presumably, you want the user to input further data in column F?
这留下了问题 (2c)，即如何在对行进行排序后将活动单元格移动到适当的行。据推测，您希望用户在 F 列中输入更多数据？
Again, the most straightforward solution would be to have this input happen first, and then do the sorting. This would have the added benefit that the user wouldn't have the input row jump around between inputting data in column E and column F. The sorting could even happen just once, after all the data has been entered by the user.
同样，最直接的解决方案是先让这个输入发生，然后再进行排序。这将带来额外的好处，即用户不会让输入行在 E 列和 F 列中的输入数据之间跳转。排序甚至可以在用户输入所有数据后只发生一次。
Of course, the above is more a design suggestion than a solution to your specific task 2c. If moving the active cell after sorting is really what you want, then the solution will inevitably be more complicated. Excel's Sortmethod does not return an index, to locate your entries after sorting. You will have to make an index / "serial number" yourself and search for it after sorting. This works:
当然，以上更多的是设计建议，而不是您特定任务 2c 的解决方案。如果排序后移动活动单元格真的是你想要的，那么解决方案难免会更加复杂。Excel 的Sort方法不返回索引，以便在排序后定位您的条目。您必须自己制作索引/“序列号”并在排序后进行搜索。这有效：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim newIndex As Long
If Not (Application.Intersect(Worksheets("Sheet1").Range("E:E"), Target) _
Is Nothing) Then
' Index the new entry in column B. (You can put the index elsewhere.)
newIndex = WorksheetFunction.Max(Range("B:B")) + 1
Target.Offset(0, -3).Value = newIndex
' Copy the entry.
Target.Offset(0, -1).Copy Destination:=Target.Offset(0, -4)
' Sort
DoSort
' Search for the new index after sorting. Select cell in column 6 (F).
Cells(WorksheetFunction.Match(newIndex, Range("B:B"), 0), 6).Select
End If
End Sub
Making an index is not strictly necessary if all your entries are unique (i.e. no duplicates); you could in principle just search for the entry itself. However, if there canbe duplicates, then searching for the entry itself (rather than its index) will be more messy, and may lead to unwanted behaviour unless it's programmed just right. I find it much cleaner to just use an index.
如果您的所有条目都是唯一的（即没有重复项），则建立索引不是绝对必要的；原则上您可以只搜索条目本身。但是，如果可能存在重复项，那么搜索条目本身（而不是其索引）将更加混乱，并且可能会导致不需要的行为，除非它的编程恰到好处。我发现只使用索引要干净得多。
回答by Tiago Cardoso
I'd suggest you to save the value that has been entered and search for this value after the sorting.
我建议您保存已输入的值并在排序后搜索该值。
Notice we may have dup data added into E column, so we'll need to store the other column's information as well until have a reliable key.
请注意，我们可能将重复数据添加到 E 列中，因此我们还需要存储其他列的信息，直到有一个可靠的键。
So, once you know the value(s) you need to search, find the cell containing the data you added in the column E (that might be in any other row by now, not only in the last one) and use it as an anchor to your other operations.
因此，一旦您知道需要搜索的值，就找到包含您在 E 列中添加的数据的单元格（现在可能在任何其他行中，而不仅仅是在最后一行中）并将其用作锚定到您的其他操作。
There are several ways to find a specific entry in a matrix (using Excel or pure VBA, as you wish). If you have problems implementing them, let us know.
有多种方法可以在矩阵中查找特定条目（根据需要使用 Excel 或纯 VBA）。如果您在实施它们时遇到问题，请告诉我们。
Having the cell address (in column E) that contains the value that has just been added, you'll use offsetfunctions to set adjacent values. Again, if you have problems implementing it, let us know your doubt.
有了包含刚刚添加的值的单元格地址（在 E 列中），您将使用offset函数来设置相邻值。同样，如果您在实施它时遇到问题，请告诉我们您的疑问。
Hope it helps :)
希望能帮助到你 ：）
Rgds
Rgds
