why is it that the vlookup macro works partially?

user_18761770 0
Viewed 28

why does the vba vlookup code is not able to populate all the data as compared to keying in the manual vlookup formula? enter image description here

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
vlookup vba excel
1 Answers

WorksheetFunction.VLookup

A Quick Fix

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
user_98140690
Related