时间戳

首页 > 解决方案 > 如何在每一行上运行相同的宏，直到表格结束？

excel - 如何在每一行上运行相同的宏，直到表格结束？

问题描述

我需要你的帮助。我正在尝试在表格的每一行上运行一个宏。我想知道与列表中所有客户的第一次和最后一次互动日期。我已经在宏上做的是从 sheet2 复制第一个日期并将其粘贴到 sheet1 上以获取第一个日期，然后使用 CTRL-Down 再次使用下一个日期来获取最后一个日期。然而，由于它不是一个循环，它只在我做的单元格上做。（下面是我的代码）。我希望代码在每个单元格上执行相同的操作，直到表格结束。

我附上了两张纸的截图。我希望我说清楚了，我希望有人可以帮助你。

表 1 表 2

Sheets("Total").Select
    Range("D6923").Select
    Selection.End(xlDown).Select
    Selection.Copy
    Sheets("Timeline").Select
    ActiveSheet.Paste
    Range("C189").Select
    Sheets("Total").Select
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Timeline").Select
    ActiveSheet.Paste
    Range("B190").Select
    Sheets("Total").Select
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Timeline").Select
    ActiveSheet.Paste
    Range("C190").Select
    Sheets("Total").Select
    Selection.End(xlDown).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Timeline").Select
    ActiveSheet.Paste

标签： excelvbaloops

解决方案

我可以看到你对此很陌生，这很好，我们都曾经！使用录制的宏是查看 excel 如何查看您当时正在做的事情的好方法，但与可能的情况相比，它的效率极低。正如 Ron 所提到的，select真的不是高效代码的朋友。例如，您的前四行可以重写为一行：

Sheets("Total").Range("D6923").End(xlDown).copy

然而，即使这也不是最好的方法。我将假设您从工作表的顶部到底部工作，并根据我认为您正在尝试做的事情回答您的问题。我还假设名为 Timeline 的工作表是工作表 1，而名为 Total 的工作表是工作表 2。在总计中，我假设可能存在任意数量的条目，而不仅仅是给出的三个示例中显示的两个条目。

Sub ExampleCode()
  'Variables, you can create and store things in VBA to make life easier for you
  Dim Wb as Workbook            'This is the workbook you are using
  Dim wsTimeline as Worksheet   'This is your worksheet called Timeline
  Dim wsTotal as Worksheet      'This is your worksheet called as Total
  Const rMin as byte = 5        'This is where the loop will start, I'm assuming row 5. As _
                                   this won't change throughout the code and we know it at the _
                                   start it can be a constant
  Dim rMax as long              'This will be the last row in your loop
  Dim r as long                 'This will be how your loop knows which row to use
  Dim timelineRow as long       'This will be the row that the data is pasted in Timeline
  Dim timelineLastRow as Long   'This is the last row of data in your timeline sheet
  
  Set Wb = Thisworkbook                   'Your whole workbook is now stored in the variable Wb
  Set wsTimeline = Wb.Sheets("Timeline")  'As the workbook was stored in Wb we can use it as _
                                             shorthand here. Now the sheet Timeline is in wsTimeline
  Set wsTotal = Wb.Sheets("Total")        'Same as above, this sheet is now stored

  rMax = wsTotal.Cells(Rows.Count, 1).End(xlUp).Row  'This is the equivalent of starting at the _
                                                        bottom row in column A and pressing _
                                                        Ctrl+Up. This takes you to the last _
                                                        row of data in column A. …(Rows.Count, 2)… _
                                                        would be column B etc.
  timelineLastRow = wsTimeline.Cells(Rows.Count, 1).End(xlUp).Row
  
  'This is the bit where you start to loop, the line below basically says "Do the code in this _
     loop for every value between rMin and rMax, each time make 'r' that value (r for row!)

  With wsTotal                                'Means that anything below starting with '.' will _
                                                 be the same as 'wsTotal.'
    For r = rMin To rMax
      'Ensure working on a line with data
      If .Cells(r, 1) = "" Then
        r = .Cells(r, 1).end(xlDown).row
        If r > rMax Then
          End With                            'Closes the With statement above as no longer needed.
          Exit For                            'Exits the loop as we have ended up beyond rMax
        End if
      End if
      
      'This will look for the person in wsTimeline and if they aren't there then add them
      If IsError(Application.Match(.Cells(r, 1), wsTimeline.Range("A3:A" & timelineLastRow), 0)) Then
        wsTimeline.Cells(timelineLastRow + 1, 1) = wsTotal.Cells(r, 1)
        timelineRow = timeLineLastRow + 1
        timelineLastRow = timelineRow
      Else
        timelineRow = Application.Match(.Cells(r, 1), wsTimeline.Range("A3:A" & timelineLastRow), 0)
      End If

      'I'm assuming that all records in 'Total' are chronologically ascending with no gaps between _
         each row for a single person.
      wsTimeline.Cells(timelineRow, 3) = .Cells(r + 2, 4)
      If .cells(r + 3, 4) <> "" then
        wsTimeline.Cells(timelineRow, 4) = .Cells(r + 2, 4).End(xlDown)
      Else
        wsTimeline.Cells(timelineRow, 4) = .Cells(r + 2, 4).End(xlDown)
      End If
      
      'Now that the data has been brought across from Total to Timeline we can move on to _
         the next row.
    Next r     'This will add one to the value stored in r and start the code again where _
                  the loop started
  End With

  'The loop has now ended having gone through every row in your worksheet called Total.
End Sub

推荐阅读