我正在寻找一种改进方法来更好地编写一段代码，查看“第二列表”中是否存在来自“第一列表”的客户端，并将数据复制到名为“找到”的工作表中。
它是这样的：
Dim row As Long, row2 As Long, found as Long Dim ID As String, prtGtId as String, GtId2 as String Application.ScreenUpdating = False prtGtId = "B" GtId2 = "D" row = 2 row2 = 2 found = 0 Do While row <= Cells(Rows.Count, prtGtId).End(xlUp).row ID = Cells(row, prtGtId) Sheets("Second List").Select Do While row2 <= Cells(Rows.Count, GtId2).End(xlUp).row If (ID = Cells(row2, GtId2)) Then Rows(row2).Select Selection.Copy Sheets("Found").Select Rows(2).Select Selection.Insert Shift:=xlDown Sheets("First List").Select Rows(row).Select Selection.Copy Sheets("Found").Select Rows(2).Select Selection.Insert Shift:=xlDown Sheets("Second List").Select found = found + 1 End If row2 = row2 + 1 Loop Sheets("First List").Select row = row + 1 row2 = 2 Loop Sheets("Blank").Select Cells(2, 3) = found Application.ScreenUpdating = True这是我的实际代码的简化版本（它更大，包含很多循环的早期退出以及按字母顺序组织），但现在我主要担心使用“.select”函数是消耗什么我的大部分处理时间。
我知道我看到有另一种方式使用
Set rng = Range(Cells(2, prtGtId),Cells(Cells(Rows.Count, GtId2).End(xlUp).row, prtGtId)) For Each Cell in rng Code Next Cell或者沿着这些路线的东西，但我似乎无法找到一个详细的教程，比“这样快得多”更进一步。
考虑到在复制到“Found”时需要保留行中的格式，有没有方法可以改变它以使其更快？
I am looking for a way to improve on a better way to write a piece of code that'll look if the there are clients from "First List" in "Second List" and copy the data over to a sheet named "Found".
It goes something like this:
Dim row As Long, row2 As Long, found as Long Dim ID As String, prtGtId as String, GtId2 as String Application.ScreenUpdating = False prtGtId = "B" GtId2 = "D" row = 2 row2 = 2 found = 0 Do While row <= Cells(Rows.Count, prtGtId).End(xlUp).row ID = Cells(row, prtGtId) Sheets("Second List").Select Do While row2 <= Cells(Rows.Count, GtId2).End(xlUp).row If (ID = Cells(row2, GtId2)) Then Rows(row2).Select Selection.Copy Sheets("Found").Select Rows(2).Select Selection.Insert Shift:=xlDown Sheets("First List").Select Rows(row).Select Selection.Copy Sheets("Found").Select Rows(2).Select Selection.Insert Shift:=xlDown Sheets("Second List").Select found = found + 1 End If row2 = row2 + 1 Loop Sheets("First List").Select row = row + 1 row2 = 2 Loop Sheets("Blank").Select Cells(2, 3) = found Application.ScreenUpdating = TrueThis is a simplified version of my actual code (which is much larger and containing a lot of early exits to the loops as well as organizing alphabetically), but right now I'm mainly concerned that using the ".select" function is what's consuming most of my processing time.
I know I've seen there's another way using
Set rng = Range(Cells(2, prtGtId),Cells(Cells(Rows.Count, GtId2).End(xlUp).row, prtGtId)) For Each Cell in rng Code Next Cellor something along those lines, but I can't seem able to find a detailed tutorial that goes further than "It's much faster this way".
Considering that I need the format in the rows to be kept when copying them over to the "Found", is there a way to change this to make it go faster?
最满意答案
激活并选择模拟用户键击，即使您将Application.ScreenUpdating设置为false，也不需要真正选择对象。 您通常应该避免使用这些方法（请参阅此处有关有关何时以及何时选择的有趣文章： http : //dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/ ）。 声明变量（设置rng ...）或直接处理对象。
Sub test() Dim row As Long, row2 As Long, found As Long Dim ID As String, prtGtId As String, GtId2 As String Application.ScreenUpdating = False prtGtId = "B" GtId2 = "D" row = 2 row2 = 2 found = 0 Do While row <= Sheets("First List").Cells(Rows.Count, prtGtId).End(xlUp).row ID = Sheets("First List").Cells(row, prtGtId) ' Sheets("Second List").Select With Sheets("Second List") Do While row2 <= .Cells(Rows.Count, GtId2).End(xlUp).row If (ID = .Cells(row2, GtId2)) Then .Rows(row2).Copy Sheets("Found").Rows(2).Insert Shift:=xlDown Sheets("First List").Rows(row).Copy Sheets("Found").Rows(2).Insert Shift:=xlDown found = found + 1 End If row2 = row2 + 1 Loop End With ' Sheets("First List").Select row = row + 1 row2 = 2 Loop Sheets("Blank").Cells(2, 3) = found Application.ScreenUpdating = True End Sub你看到少了几行，没有更多的选择。 另请参阅如何使用“with”语句（在此用作示例），这可能非常有用。 （我假设你在激活宏表单的时候启动你的宏，这就是为什么我添加表单（“第一列表”））编程这种方式也避免了这种错误（所以你可以启动你的宏而不用担心激活表单）
Activate and select emulate user keystrokes and even if you set Application.ScreenUpdating to false, you don't need to really select objects. You should generally avoid these methods (see here an interesting article about why and when to select:http://dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/). Declare variables (set rng...) or deal directly with the objects instead.
Sub test() Dim row As Long, row2 As Long, found As Long Dim ID As String, prtGtId As String, GtId2 As String Application.ScreenUpdating = False prtGtId = "B" GtId2 = "D" row = 2 row2 = 2 found = 0 Do While row <= Sheets("First List").Cells(Rows.Count, prtGtId).End(xlUp).row ID = Sheets("First List").Cells(row, prtGtId) ' Sheets("Second List").Select With Sheets("Second List") Do While row2 <= .Cells(Rows.Count, GtId2).End(xlUp).row If (ID = .Cells(row2, GtId2)) Then .Rows(row2).Copy Sheets("Found").Rows(2).Insert Shift:=xlDown Sheets("First List").Rows(row).Copy Sheets("Found").Rows(2).Insert Shift:=xlDown found = found + 1 End If row2 = row2 + 1 Loop End With ' Sheets("First List").Select row = row + 1 row2 = 2 Loop Sheets("Blank").Cells(2, 3) = found Application.ScreenUpdating = True End Subyou see few lines less and no more select. See also how works "with" statement (used here as an example) which can be very useful. (I assume you launch your macro with sheet "First List" activated, that's why I add Sheets("First List")) Programming that way also avoid this kind of error (so you can launch your macro without worrying about wich sheet is activate)
更多推荐
发布评论