为什么Vlookup宏可以部分工作？

Question

与手动Vookup公式中的键合相比，为什么VBA VLOOKUP代码无法填充所有数据？

Sub vlook()

Dim sSht As Worksheet
Dim pSht As Worksheet
Dim PLastRow As Long
Dim dataLastRow As Long
Dim rng As Range
Dim i As Long

Set sSht = ThisWorkbook.Worksheets("summarised_table")
Set pSht = ThisWorkbook.Worksheets("Pivot")

dataLastRow = pSht.Range("A" & Rows.Count).End(xlUp).Row
PLastRow = sSht.Range("A" & Rows.Count).End(xlUp).Row

Set rng = sSht.Range("A2:B" & dataLastRow)

For i = 2 To PLastRow

On Error Resume Next

pSht.Range("B" & i).Value = Application.WorksheetFunction.VLookup( _
    pSht.Range("A" & i).Value, rng, 2, False)
    

Next i

End Sub

Answer 1

worksheetfunction.vlookup

快速修复

Sub vlook()

    ' =VLOOKUP(p!A2,s!A:B,2,FALSE)

    ' s - Summarized Table
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("summarised_table")
    Dim sLastRow As Long
    sLastRow = sws.Range("A" & sws.Rows.Count).End(xlUp).Row
    Dim srg As Range: Set srg = sws.Range("A2:B" & sLastRow)
    
    ' p - Pivot
    Dim pws As Worksheet: Set pws = ThisWorkbook.Worksheets("Pivot")
    Dim pLastRow As Long
    pLastRow = pws.Range("A" & pws.Rows.Count).End(xlUp).Row
    
    Dim i As Long
    
    For i = 2 To pLastRow
        On Error Resume Next
            pws.Range("B" & i).Value = WorksheetFunction.VLookup( _
                pws.Range("A" & i).Value, srg, 2, False)
        On Error GoTo 0
    Next i

    MsgBox "Lookup finished.", vbInformation

End Sub