In Excel apply formula by vlookup from Sheet1 to Sheet2 in VBA

19 Views Asked by At

In my Excel (.xlsm) sheet1 B column empty Apply formula in G column by vlookup get data from sheet2. It shows error "Subscript Out Of Range"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow As Long, i As Long
    Dim lookupValue As Variant, result As Variant
    
    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Find the last row of column B in Sheet1
    lastRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    
    ' Loop through each row in column B of Sheet1
    For i = 3 To lastRow ' Assuming data starts from row 3
        If ws1.Cells(i, "B").Value = "" Then ' Check if column B cell is empty
            ' Get the value to look up from column B of Sheet1
            lookupValue = ws1.Cells(i, "B").Value
            ' Apply VLOOKUP to find the value from Sheet2
            result = Application.VLookup(lookupValue, ws2.Range("J:K"), 2, False)
            ' If VLOOKUP finds a match, paste the value as value
            If Not IsError(result) Then
                ws1.Cells(i, "G").Value = result
            End If
        End If
    Next i
    
    ' Cleanup
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

I have tried but its shows error.

0

There are 0 best solutions below