如何在 vba 的内循环中使用 if 加速双循环? [英] How do I speed up a double for loop with if in the inner loop in vba?

问题描述

我编写此代码是为了在工作表q1"的一列中搜索与工作表Complete Car"中另一列元素的匹配项.由于我有 3000 行要检查 Complete Car，而在 q1(内循环)中有 1500 行，是否有关于如何更有效地编写此内容的建议?

I've written this code to search a match in a column of sheet "q1" with the elements of another column in sheet "Complete Car". Since I have 3000 rows to check in Complete Car and 1500 in q1 (inner loop), is there any suggestion on how to write this more efficiently?

代码如下:

Sub PopulateData()

 Sheets("Q1").Visible = True

  Dim i As Integer
  Dim j As Integer

  For i = 4 To 3000

        For j = 2 To 1500


        If Worksheets("Complete Car").Cells(i, 2) = Worksheets("Q1").Cells(j, 21) Then

           Worksheets("Complete Car").Cells(i, 32) = Worksheets("Q1").Cells(j, 30)

           End If

        Next j

  Next i


  Sheets("Q1").Visible = False

推荐答案

使用 Variant 数组

Use Variant arrays

Sheets("Q1").Visible = True

With Worksheets("Complete Car")
    Dim vlue() As Variant
    vlue = .Range(.Cells(4, 2), .Cells(3000, 2))

    Dim out() As Variant
    ReDim out(1 To UBound(vlue, 1), 1 To 1)
End With

With Worksheets("Q1")
    Dim lkup() As Variant
    lkup = .Range(.Cells(2, 21), .Cells(1500, 30))
End With

Dim i As Long
For i = LBound(vlue, 1) To UBound(vlue, 1)
    Dim j As Long
    For j = LBound(lkup, 1) To UBound(lkup, 1)
        If vlue(i, 1) = lkup(j, 1) Then
            out(i, 1) = lkup(j, 10)
            Exit For
        End If
    Next j
Next i

Worksheets("Complete Car").Cells(4, 32).Resize(UBound(out, 1), UBound(out, 2)).Value = out

Sheets("Q1").Visible = False

这篇关于如何在 vba 的内循环中使用 if 加速双循环?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！