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

Question

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(vWSs)
    If CBool(Application.CountIf(.Sheets(vWSs(v)).Range("A2:ZA2"), mf_pLN_TEXT)) Then
        Set Mrange = .Sheets(vWSs(v)).Range("A2:ZA2")
        Set Vrange = .Sheets(vWSs(v)).Range("A:ZA")
        mf_pLN = Application.Match(mf_pLN_TEXT, Mrange, 0)
Exit For
    End If
Next v
For i = 2 To lastrow
    wsMaster.Cells(i, 3) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_pLN, 0)
Next i
Set Mrange = Nothing
Set Vrange = Nothing

The `Application.Vlookup` returns an error value `Error 2042` when the result cannot be found, and should print this in the destination cell as `#N/A`. The error you describe must be due to something other than the vlookup not found. — David Zemens, Feb 09 '15 at 19:45
Can you indicate which of the two VLOOKUP statements is causing the failure? — David Zemens, Feb 09 '15 at 19:56
it seems to be either, or if I add more subsequent loops with different variables, but it doesn't find a match, it will give the error on the first one with that problem that it comes across. But if I make a column with the heading I need, it will find it and not give me that error. — msim, Feb 09 '15 at 19:59
hmmm, if I comment out the `Set Mrange and Vrange = Nothing`, it doesn't give me the error, but it outputs `#VALUE` in the column it couldn't find — msim, Feb 09 '15 at 20:01
Yeah that has something to do with it. It's really difficult to understand what you're trying to do here, which is why it's taking me long to figure out the problem... — David Zemens, Feb 09 '15 at 20:03
This question i posted earlier has more of an explanation of what I'm doingand how I got to the code that I have: http://stackoverflow.com/questions/28414831/excel-vba-out-of-memory-error-on-if-statement/28415288?noredirect=1#comment45166782_28415288 — msim, Feb 09 '15 at 20:11
don't need it, see my answer below :) BTW this would be a good bit of code to put on Code Review, it could be made more modular so you don't have to keep copying blocks of code to reuse them, you can use variables more efficiently, etc. — David Zemens, Feb 09 '15 at 20:13
@DavidZemens Code should be posted to Code Review only after errors have been fixed. — 200_success, Feb 09 '15 at 20:22

score 1 · Accepted Answer · answered Feb 09 '15 at 20:11

Your VLookup statements will fail with the described error message if the VRange object is nothing.

It's difficult to understand what you're trying to do but I will try to break it down. Here you have a loop that exits on the first instance of the CBool expression returning True.

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

At this point assuming the loop is exited by the Exit For statement, the VRange object should exist and be a valid range, so the next loop should not fail:

    For i = 2 To lastrow
        wsMaster.Cells(i, 2) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_iA, 0)
    Next i

HOWEVER if that first loop completes without ever "finding" the value mf_iA_Text, then the VRange object is not a valid range, and that would cause the error.

Suggest to revise by putting the second loop inside the first:

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)

        For i = 2 To lastrow
            wsMaster.Cells(i, 2) = Application.VLookup(wsMaster.Cells(i, 1), Vrange, mf_iA, 0)
        Next i

        Exit For
    End If
Next v

Hi David, your code revision worked in the sense that it returned nothing if the column is not found. Not #N/A, just blank, but I actually think that is preferred and I might make it so it gives the user a msgbox saying that it couldn't find anything for that column anyway. So, I am going to mark this as correct. Thanks so much for your help!! — msim, Feb 10 '15 at 20:20

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

1 Answers1