remove #NA from VLookup macro result...?

[blist]

My Lookup value range contains many blank cells I'd like to remain blank. This code works to populate that range as needed, but the blank cells all show a #NA result. How can I edit this (or another?) macro to leave the blank cells empty?

Sub EQL_Lookup()
Dim LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

With WorksheetFunction

     Range("L2:P" & LastRow).Value = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
    
End With
End Sub

 

[Jonmo1]

Try

Sub EQL_Lookup()
Dim LastRow As Long, x As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
 
With Application
    x = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
 
    If Iserror(x) Then 
        Range("L2:P" & LastRow).Value = ""
    Else
        Range("L2:P" & LastRow).Value = x
    End If
End With
End Sub

 

[Jeffrey Smith]

I don't have the range for the lookup so I can't test this

Sub EQL_Lookup()
Dim LastRow As Long
Dim V As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
With WorksheetFunction
  V = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
  If V = "#NA" Then V = ""
  
  Range("L2:P" & LastRow).Value = V
    
End With
End Sub

 

[blist]

Thanks to all...

jonmo1, I got the same result with your code as mine...lookup value range correctly populated with titles, and all blank cells still show #NA.

Jeffery Smith, your code showed a Type Mismatch at the highlight below when run. My edits are strictly guesses, and I haven't been able to make it work yet.

Sub EQL_Lookup()
Dim LastRow As Long
Dim V As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
     With WorksheetFunction
      V = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
 [highlight] If V = "#NA" Then[/highlight] V = "" 
  
     Range("L2:P" & LastRow).Value = V
    
End With
End Sub

 

[Jonmo1]

and all blank cells still show #NA

I don't understand.
The posted code only does 1 single vlookup...what do you mean ALL...All of what?


Something tells my you've posted a "simplified" version of your actual code.

Can you post your actual complete code?
And a detailed explaination of what you want to do?

 

[Jeffrey Smith]

This is kind of clunky, but it works

Sub EQL_Lookup()
Dim LastRow As Long
Dim V As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
     With WorksheetFunction
      On Error GoTo NoVal
      V = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
  
     Range("L2:P" & LastRow).Value = V
    
End With
Exit Sub
: NoVal
Range("L2:P" & LastRow).Value = ""
End Sub

 

[blist]

jonmo1, my apologies for any misunderstandings...

I indeed posted the complete code. As stated in my original post, my Lookup value range contains many blank cells I'd like to remain blank. This code works to populate that range as needed, but the originally blank cells (before running the posted code) all show a #NA result after running the code.

Many thanks for your time and trouble!

 

[Jonmo1]

OK, think I get it..

Try

Sub EQL_Lookup()
Dim LastRow As Long, x As Variant

LastRow = Range("A" & Rows.Count).End(xlUp).Row
For Each c In Range("L2:P" & LastRow)
    x = Application.VLookup(c, Sheets(2).Range("A1:C" & LastRow), 2, False)
    If Not IsError(x) Then c.Value = x
Next c
End Sub

 

[blist]

Jeffrey Smith, thanks. At the red highlight, VBE says Sub or Function not defined. What does that ": NoVal" phrase mean or refer to in this context...?

Sub EQL_Lookup()
Dim LastRow As Long
Dim V As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
     With WorksheetFunction
      On Error GoTo NoVal
      V = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
  
     Range("L2:P" & LastRow).Value = V
    
End With
Exit Sub
: [highlight]NoVal[/highlight]
Range("L2:P" & LastRow).Value = ""
End Sub

 

[Jeffrey Smith]

Sorry, the colon is in the wrong place. And I forgot to resume next. Always in a hurry to get my post up because there are so many people willing to help.

Sub EQL_Lookup()
Dim LastRow As Long
Dim V As Variant
LastRow = Range("A" & Rows.Count).End(xlUp).Row
  With WorksheetFunction
    On Error GoTo NoVal
    Range("L2:P" & LastRow).Value = .VLookup(Range("L2:P" & LastRow), Sheets(13).Range("A1:C" & LastRow), 2, False)
  End With
Exit Sub
: NoVal
Range("L2:P" & LastRow).Value = ""
Resume Next
End Sub