VBA Code:
ws1.Range("K4", "K" & LastRow).Value _
= WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:C182"), 1, 0)
Hi, the above vlookup code works. But what code should I add so that if the code return #NA it show a blank cell.
For the same code, ws2.Range("C2:C182"), how can I make it dynamic? Now it is hard coded. Meaning that whenever I add a new row in worksheets (ws2) it will auto update the rows in the formula. So that I don’t have to go into code and update C2:C1xx every time when there is a new row.
Thanks in advance. The full code is below
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LastRow As Long
Dim LastRow1 As Long
Set wb = ThisWorkbook
Set ws1 = wb.Sheets("CHECKS")
Set ws2 = wb.Sheets("LEADERS")
LastRow = ws1.Cells(Rows.Count, 1).End(xlUp).Row
LastRow1 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws1.Range("K4", "K" & LastRow).Value _
= WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:C182"), 1, 0)
ws1.Range("L4", "L" & LastRow).Value _
= WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("F2:F182"), 1, 0)
End Sub