Excel VBA：排序，然后复制和粘贴 [英] Excel VBA: Sort, then Copy and Paste

问题描述

所有，我需要编写一个宏，执行以下操作：

1. 数据进入列E中的最后一个空白单元格，按照列E按降序排序整个工作表




2. 工作表排序后：

   
   

   2a。将单元格立即复制到相邻单元格的单元格的首个之前

   
   










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





2c。将光标移动到紧接第一次输入数据的单元格的右侧的相邻单元格

下面，我列出了有关入门代码的排序。但是，我不能然后获取代码复制，粘贴和移动正确。我最常见的问题是：数据输入后，行移动，但光标停留在首次输入数据的行中。谁能帮忙？ （我甚至不能得到这个帖子的缩进权）！

  Private Sub Worksheet_Change（ByVal Target As Range）
如果不是（Application.Intersect（Worksheet（Sheet1）。Range（E：E），Target）Is Nothing）Then 
 DoSort 
 End If 
 End Sub 
 
 Private Sub DoSort（）
工作表（Sheet1）。范围（A：E）排序Key1：=工作表（Sheet1）。范围（E1），Order1 ：= xlDescending，Header：= xlYes 
 End Sub 
  

解决方案

关于1,2a和2b：在排序之前进行复制是比较直接的。这样，复制的值将与其余值一起排序。

  Private Sub Worksheet_Change（ByVal Target As Range）
如果没有（Application.Intersect（Worksheets（Sheet1） .Range（E：E），目标）_ 
不是）然后
'第一个副本
 Target.Offset（0，-1）.Copy Destination：= Target.Offset 0，-4）
'然后排序
 DoSort 
结束If 
 End Sub 
  

这留下了在行排序后如何将活动单元格移动到相应行的问题（2c）。大概您希望用户在列F中输入进一步的数据？

同样，最直接的解决方案是首先发生此输入，然后进行排序。这将有额外的好处，用户不会在列E和列F中输入数据之间跳过输入行。在用户输入所有数据之后，排序甚至可能只发生一次。

当然，上述更多的是设计建议，而不是解决您的具体任务2c。如果在排序后移动活动单元格真的是你想要的，那么解决方案将不可避免地会变得更加复杂。 Excel的 Sort 方法不返回索引，以便在排序后找到您的条目。您必须自己制作索引/序列号，并在排序后搜索。这样做：

  Private Sub Worksheet_Change（ByVal Target As Range）
 Dim newIndex As Long 
如果不是（Application.Intersect（Worksheet（Sheet1）。Range（E：E），Target）_ 
 Is Nothing）然后
'在列B中索引新条目（可以将
 newIndex = WorksheetFunction.Max（Range（B：B））+ 1 
 Target.Offset（0，-3）.Value = newIndex 
'复制条目。 
 Target.Offset（0，-1）.Copy目的地：= Target.Offset（0，-4）
'排序
 DoSort 
'排序后搜索新索引。选择第6栏（F）中的单元格。 
单元格（WorksheetFunction.Match（newIndex，Range（B：B），0），6）。选择
 End If 
 End Sub 
  

如果所有条目都是唯一的（即没有重复），则不需要进行索引;原则上，您可以搜索条目本身。然而，如果可以重复，那么搜索条目本身（而不是其索引）会更加凌乱，并且可能导致不必要的行为，除非它被编程正确。我发现使用索引更为干净。

All, I need to write a macro that does the following:

1. On entry data into the last blank cell in column E, sort the entire worksheet by column E in descending order

2. 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

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 right of the cell into which the data was first entered

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

解决方案

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.

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?

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.

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:

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.

这篇关于Excel VBA：排序，然后复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！