如果使用FOR和Next循环在big excel文件中的列中显示，则从一张工作表复制数据并粘贴到主跟踪器的确切行中太慢

Thanks for accepting me in this forum.

感谢您在此论坛中接受我。

I have a excel sheet which contains more than 90,000 rows as master traker.

我有一个excel表,其中包含超过90,000行作为主跟踪器。

my code is

我的代码是

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
Dim mysegment As String
lastrow1 = wb3.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To lastrow1
myname = wb3.Sheets(1).Cells(i, "A").Value
mysegment = wb3.Sheets(1).Cells(i, "B").Value
Wb2.Sheets(1).Activate
lastrow2 = Wb2.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row

For j = 2 To lastrow2

If Wb2.Sheets(1).Cells(j, "A").Value = myname And Wb2.Sheets(1).Cells(j, "B").Value = mysegment Then
wb3.Sheets(1).Activate
wb3.Sheets(1).Range(Cells(i, "C"), Cells(i, "M")).Copy
Wb2.Sheets(1).Activate
Wb2.Sheets(1).Range(Cells(j, "C"), Cells(j, "M")).Select
ActiveSheet.Paste
End If

Next j
Application.CutCopyMode = False
Next i

I need to update the Column C to K of master tracker using individual excel sheet which contains more than 1200 rows if the column A and B match.

如果A列和B列匹配,我需要使用包含超过1200行的单个Excel工作表更新主跟踪器的C列到K.

I have used For and next loop for this. However, it is taking so much time approximately 1 hour** to complete the task.

我已经使用了For和next循环。但是,完成任务需要花费大约1小时**的时间。

It will be grateful, if you resolve this issue.

如果您解决此问题,将不胜感激。

2 个解决方案

#1

Maybe try to disable screen update before loop :

也许尝试在循环之前禁用屏幕更新:

Application.ScreenUpdating = False

And don't forget to enable them after loop, to let user modify the excel file :

并且不要忘记在循环后启用它们,让用户修改excel文件:

Application.ScreenUpdating = True

#2

General Approach

1. Create a separate worksheet with a Microsoft SQL Query which make the relevant updates. OLEDB is significantly faster than VBA.

   使用Microsoft SQL查询创建单独的工作表,以进行相关更新。 OLEDB明显快于VBA。

2. Copy the separate worksheet onto the master tracker.

   将单独的工作表复制到主跟踪器上。

If this is not feasible (for some reason) and it's an issues of code performance you can try many way of improving code performance: see my post here.

如果这不可行(由于某种原因)并且这是代码性能问题,您可以尝试多种方法来提高代码性能:请参阅我的帖子。

Last resort - VBA multithreading. See my post here.

最后的手段 - VBA多线程。在这里看我的帖子。

Specific findings

Try modifying your code as follows:

尝试按如下方式修改代码:

Application.ScreenUpdating = False'!!!!
Application.Calculation = xlCalculationManual'!!!!

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long, myname As String, mysegment As String, ws2 as worksheet, ws3 as worksheet
Set ws3 = wb3.Sheets(1): Set ws2 = wb2.Sheets(1)
lastrow1 = ws3.Range("A" & Rows.Count).End(xlUp).Row
lastrow2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow1
  myname = ws3.Cells(i, 1).Value
  mysegment = ws2.Cells(i, 2).Value

  For j = 2 To lastrow2
    If ws2.Cells(j, 1).Value = myname And ws2.Cells(j, 2).Value = mysegment Then
      ws3.Range("C" & i & ":M" & i)= ws2.Range("C" & i & ":M" & i)
    End If
  Next j
Next i

Application.Calculation = xlCalculationAutomatic'!!!!
Application.ScreenUpdating = True'!!!!

Further performance

I would "replace" the master tracker with a Microsoft Query simply writing a SQL like (draft):

我会用Microsoft Query“替换”主跟踪器,只需编写类似(草稿)的SQL:

SELECT Iif(IsNull(slave.A),master.A,slave.A), 
   Iif(IsNull(slave.A),master.B,slave.B),
   Iif(IsNull(slave.A),master.C,slave.C),
   etc......
FROM [Sheet1$] as master 
LEFT OUTER JOIN `C:\slave.xlsx`.`Sheet1$1` as slave 
ON master.A = slave.A and master.B = slave.B

This would be as fast as it gets. Probably a couple seconds maximum. Feel free to play with my SQL Add-in to try: link.

这将是最快的。可能最多几秒钟。随意使用我的SQL加载项来尝试:链接。