一个更快的替代我的 vlookup 代码块? [英] A faster alternative to my vlookup block of code?

问题描述

表 'kw30'、'kw60' 和 'kw90' 大约有 20k 行.bulkexport"表大约有 30 万行.

Sheets 'kw30', 'kw60', and 'kw90' are about 20k lines. sheet 'bulkexport' is about 300k lines.

仅此部分就需要大约 20 分钟的时间来执行.

This section alone takes about 20 minutes to execute.

有没有更快的方法来解决这个问题或重构它?我试图想办法将bulkexport"表的底部三分之一分成多个部分，以便 vlookup 一次只查看一小部分...

Is there a faster way to approach this or restructure it? I was trying to think of a way to break the bottom third with the 'bulkexport' sheet into multiple sections so that vlookup is only looking at small portions at a time...

感谢任何输入.

谢谢！！数据

    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
    

推荐答案

如果您切换到 Match() 来定位正确的行(您只需要每个源数据集每行执行一次...) - 然后您可以直接从该行中提取所需的单元格值.

Your current approach would be a little faster if you switched to Match() to locate the correct row (which you only need to do once per row per source dataset...) - then you can pull the required cells values directly from that row.

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

这篇关于一个更快的替代我的 vlookup 代码块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！