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.