Excel VBA无效的过程调用或vlookup找不到结果时的参数

Question

我已经在这里编写了一些代码，由极其有用的用户Jeeped帮助。唯一的问题是，我收到错误Invalid Procedure Call or Argument当查找不找到结果。如果发现结果，它似乎很好。

我希望它只是返回＃N /好像它找不到结果。你能帮我解决这个问题吗？另外我还有500列可以做到这一点，所以如果有更简单的方法来解决这个问题，那就太棒了。我仍然在学习VBA。非常感谢。如果VRange对象是什么

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 

Answer 1

你VLookup语句将失败，所描述的错误消息。

很难理解你想要做什么，但我会尽力将其分解。这里有一个循环，返回True的CBool表达式的第一个实例。

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 

此时假设环路由Exit For声明退出，VRange对象应该存在并且是一个有效的范围内，所以下一个循环不应失败：

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

无论其如果第一个循环完成而没有“找到”值mf_iA_Text，那么VRange对象不是有效范围，那会导致err要么。

推荐通过将第二循环中的第一内部修改：

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