让日常工作更有效率？

Question

我有以下代码来查找属于单元格C3中的值的值(以及更低的值)：

aantalrijen = .Range("A2", .Range("A2").End(xlDown)).Cells.Count
    For I = 2 To aantalrijen + 1
        For J = 108 To 112
            For Each cell In .Range(.Cells(2, J), .Cells(aantalrijen, J)).Cells
                cell.Value = Application.VLookup(.Cells(I, 3), Sheets("omzet").Range("C:DH"), J - 2, 0)
            Next cell
        Next J
    Next I

我知道这并不是取得预期结果的最有效方法。我应该如何调整代码，使其成为最有效的？

更新：

就目前而言，我对这个结果感到满意：

aantalrijen = .Range("A2", .Range("A2").End(xlDown)).Cells.Count
    For J = 108 To 112
        For I = 2 To aantalrijen
            .Cells(I, J).Value = Application.VLookup(.Cells(I, 3), Sheets("omzet").Range("C:DH"), J - 2, 0)
        Next I
    Next J

End With

现在它对我来说已经足够快了，它返回了所需的结果。

Answer 1

在此：

Option Explicit
Sub Test()

    Dim arrSource, arrData, i As Long, j As Long, ColI As Long, ColF As Long
    Dim DictMatches As New Scripting.Dictionary
    Dim DictHeaders As New Scripting.Dictionary

    With ThisWorkbook
        arrSource = .Sheets("omzet").UsedRange.Value
        arrData = .Sheets("SheetName").UsedRange.Value 'change this for the worksheet you are working on
    End With

    For i = 1 To UBound(arrSource, 2) 'this will store the headers position
        DictHeaders.Add arrSource(1, i) 'this will throw an error if you have any duplicate headers
    Next i

    For i = 2 To UBound(arrSource) 'this will store the row position for each match
        DictMatches.Add arrSource(i, 3), i 'this will throw an error if you have any duplicates
    Next i

    'Here you can change where you want to evaluate your data
    ColI = 108
    ColF = 112

    For i = 2 To UBound(arrData) 'loop through rows
        For j = ColI To ColF 'loop through columns
            arrData(i, j) = arrSource(DictMatches(arrData(i, 3), DictHeaders(1, j)))
        Next j
    Next i

    'Paste the arrData back to the sheet
    ThisWorkbook.Sheets("SheetName").UsedRange.Value = arrData

End Sub

这是最快的方法，为什么？

1. 将两个工作表存储到数组中，从那时起只使用数组(这意味着处理内存，因此工作速度更快)
2. 使用excel函数总是会减慢处理速度，相反，我们将所有索引值存储在omzet工作表的行和标头上，因此，当您指向工作表上C列的值时，它将给出结果，而无需计算任何内容。

这里：arrSource(DictMatches(arrData(i, 3), DictHeaders(1, j)))，我们给出了一个行位置和列位置。

DictMatches(arrData(i, 3)会给出匹配的行，在该行中找到匹配项。DictHeaders(1, j)将返回在字典中找到该标题的列。

注意:要使字典正常工作，需要对引用进行Microsoft Scripting Runtime库的检查。字典也是Case Sensitive所以Hello <> hello。