Search code examples
excelvba

Vlookup Range of Data to Another Sheet Using VBA

I'm new to vba and wonder if I could do vlookup range of data from column C in sheet 1 to column D in sheet 2.

This is my current code and when I run it, VBA doesn't give any run time error nor any results as well:

Sub look()
    Dim lw As Range
    Set lw = ThisWorkbook.Sheets("NO PK").Range("P" & Rows.Count).End(xlDown)
    
    For i = 3 To lw
    Worksheets("Sheet5").Cells(i, 3).Value = Application.WorksheetsFunction.VLookup(Worksheets("NO PK").Cells(i, 1).Value, Worksheets("Sheet5").Range("A:P"), 16, 0)
    Next
End Sub

I wish to make it dynamic by using for next loop. But, I'm open to other ideas. Thank you in advance!

Solution

  • A VBA VLookup

    enter image description here

    A Quick Fix

    Option Explicit

Sub LookupInSheet5()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet5")
    Dim srg As Range: Set srg = sws.Columns("A:P")
    
    Dim dws As Worksheet: Set dws = wb.Sheets("NO PK")
    Dim dlRow As Long: dlRow = dws.Cells(dws.Rows.Count, "A").End(xlUp).Row
    
    Dim i As Long
    
    For i = 3 To dlRow
        dws.Cells(i, "C").Value _
            = Application.VLookup(dws.Cells(i, "A").Value, srg, 16, 0)
    Next i
    
    MsgBox "Lookup has finished.", vbInformation
    
End Sub