Excel VBA Invalid Procedure Call or Argument when vlookup finds no result

manpreet Best Answer 2 years ago

I've written some code here, aided by the extremely helpful user Jeeped. Only problem is that I'm getting the error Invalid Procedure Call or Argument when the vlookup does not find a result. If it finds a result, it seems to be fine.

I want it to just return #N/As if it can't find a result. Can you help me fix this? Also I have 500 columns to do this with so if there's a much easier way to write it to account for that, that'd be great. I'm still learning VBA. Thanks so much.

    Dim v As Long, vWSs As Variant, Mrange As Range, Vrange As Range
    Dim wsMaster As Worksheet: Set wsMaster = Workbooks("LBImportMacroTemplate.xlsm").Worksheets("MasterTab")
    Dim mf_iA_TEXT As String: mf_iA_TEXT = "iA"
    Dim mf_pLN_TEXT As String: mf_pLN_TEXT = "pLN"
    'ET CETERA
    vWSs = Array("B", "E", "L", "I", "T")
    With Workbooks("LBImportMacroTemplate.xlsm")
        Set Mrange = Nothing: Set Vrange = Nothing
'
With ActiveSheet
    lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
    MsgBox lastrow
End With
'
For v = LBound(vWSs) To UBound(vWSs)
    If CBool(Application.CountIf(.Sheets(vWSs(v)).Range("A2:ZA2"), mf_iA_TEXT)) Then
        Set Mrange = .Sheets(vWSs(v)).Range("A2:ZA2")
        Set Vrange = .Sheets(vWSs(v)).Range("A:ZA")
        mf_iA = Application.Match(mf_iA_TEXT, Mrange, 0)
Exit For
    End If
Next v
For i = 2 To lastrow
    wsMaster.Cells(i, 2) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_iA, 0)
Next i
Set Mrange = Nothing
Set Vrange = Nothing
'
For v = LBound(vWSs) To UBound(
                                                

                                                                                                        REPLY
                                                    
                                                
                                                


                                                    

                                                        
                                                            0
                                                            views 

                                                            0
                                                                likes 
                                                            
                                                            

                                                            0
                                                            shares

                                                            


    

         Facebook
        
         Twitter
        
        
         Linked In 
        
          WhatsApp