Vba vlookup to return back not #NA

[Boboka]

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

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

 

[mohadin]

Hi
Try

ws1.Range("K4", "K" & LastRow).Value _
= Application.IfNa(WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:C" & LastRow1), 1, 0), "")

 

[mohadin]

Or

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) _
= Application.IfNa(WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:C" & LastRow1), 1, 0), "")

ws1.Range("L4", "L" & LastRow).Value _
= Application.IfNa(WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("F2:F" & LastRow1), 1, 0), "")

 

[Boboka]

Thanks, it work for the first vlookup. But I realise that the second formula last part

ws2.Range(“F2:F” & LastRow1), 1, 0), “ “)

need to change F2:F to a look up range/array.

ws1.Range("L4", "L" & LastRow).Value _ 
= Application.IfNa(WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:F182" & LastRow1), 4, 0), "")

this work but how to make C2:F182 dynamic?

Thanks in advance.

 

[Larry Haydn]

Try:

ws1.Range("L4", "L" & LastRow).Value _
= Application.IfNa(WorksheetFunction.VLookup(ws1.Range("D4", "D" & LastRow), ws2.Range("C2:F" & LastRow1), 4, 0), "")

 

[Boboka]

Thank you Larry & Mohadin. Now it work great..

 

[mohadin]

Glad we could help
Be happy and safe