excel - 我的 vlookup 代码块的更快替代方案？

Question

表格“kw30”、“kw60”和“kw90”大约有 20k 行。表“bulkexport”大约有 300k 行。
仅此部分执行大约需要 20 分钟。
有没有更快的方法来解决或重组它？我试图想办法将“bulkexport”表的底部三分之一分成多个部分，以便 vlookup 一次只查看一小部分......
任何输入表示赞赏。
谢谢!!
德国之声

    Sheets("kw90").Select
    For i = 2 To kw90rowcount
        On Error Resume Next

        
        Range("ac" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw60").Range("x2:y" & kw60rowcount), 2, False)
        Range("ad" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw60").Range("x2:z" & kw60rowcount), 3, False)
        Range("ae" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw60").Range("x2:aa" & kw60rowcount), 4, False)
        Range("ai" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw60").Range("x2:ab" & kw60rowcount), 5, False)
        Range("aj" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw60").Range("x2:ac" & kw60rowcount), 6, False)
        
        Range("af" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw30").Range("x2:y" & kw30rowcount), 2, False)
        Range("ag" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw30").Range("x2:z" & kw30rowcount), 3, False)
        Range("ah" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw30").Range("x2:aa" & kw30rowcount), 4, False)
        Range("ak" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw30").Range("x2:ab" & kw30rowcount), 5, False)
        Range("al" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("kw30").Range("x2:ac" & kw30rowcount), 6, False)
        
        
        Range("y" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("bulkexport").Range("ac2:ad" & bulkexportrowcount), 2, False)
        Range("z" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("bulkexport").Range("ad2:ae" & bulkexportrowcount), 3, False)
        Range("aa" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("bulkexport").Range("ae2:af" & bulkexportrowcount), 4, False)
        Range("ab" & i).Value = Application.WorksheetFunction.VLookup(Range("x" & i).Value, Worksheets("bulkexport").Range("af2:ag" & bulkexportrowcount), 5, False)
        
        
    Next i
    

Answer 1

如果您切换到 Match()，您当前的方法会更快一些。找到正确的行(您只需为每个源数据集的每行执行一次...) - 然后您可以直接从该行中提取所需的单元格值。

Sub UseMatch()
   
    Dim i As Long, m, rw As Range, xVal, arr
    Dim wsKW60 As Worksheet
    
    Set wsKW60 = Worksheets("kw60")
    
    For i = 2 To kw90rowcount
        
        Set rw = Sheets("kw90").Rows(i)
        xVal = rw.Columns("X").Value
        
        'find the row once
        m = Application.Match(xVal, wsKW60.Range("x1:x" & kw60rowcount), 0)
        If Not IsError(m) Then
            arr = wsKW60.Cells(m, "Y").Resize(1, 5).Value 'got a row - read all values in one operation
            rw.Columns("AC").Value = arr(1, 1)            'then assign values from the array
            rw.Columns("AD").Value = arr(1, 2)
            rw.Columns("AE").Value = arr(1, 3)
            rw.Columns("AI").Value = arr(1, 4)
            rw.Columns("AJ").Value = arr(1, 5)
        End If
        
        'next sheets ...

    Next i
End Sub