Excel VBA：排序，然后复制和粘贴

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

将输入数据放入E列的最后一个空白单元格中,按E列按降序对整个工作表进行排序

Once the worksheet is sorted:

工作表排序后:

2a. Copy the cell to the adjacent cell immediately to the left of the cell into which the data was first entered

2A。将单元格复制到第一个输入数据的单元格左侧的相邻单元格

2b. Paste the copied data into the first column of the same row from which the data was originally entered

2B。将复制的数据粘贴到最初输入数据的同一行的第一列

2c. Move the cursor to the adjacent cell immediately to the right of the cell into which the data was first entered

2C。将光标移动到第一个输入数据的单元格右侧的相邻单元格

Below, I show the sort on entry code, which works. However, I cannot then get 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

2 个解决方案

#1

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 Sort method 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 can be 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.

如果所有条目都是唯一的(即没有重复),则不一定要制作索引;你原则上可以只搜索条目本身。但是,如果可能存在重复,那么搜索条目本身(而不是其索引)将更加混乱,并且可能导致不必要的行为,除非它的编程恰到好处。我觉得使用索引要简洁得多。

#2

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 offset functions to set adjacent values. Again, if you have problems implementing it, let us know your doubt.

使用包含刚刚添加的值的单元格地址(在E列中),您将使用偏移函数来设置相邻值。同样,如果您在实施它时遇到问题,请告诉我们您的疑问。

Hope it helps :)

希望能帮助到你 :)

Rgds

#1