在 VBA 中使用 vlookup 的 IfError 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40685035/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IfError function using vlookup in VBA
提问by Marie
I am trying to use a vlookup function to search for a value in multiple worksheets that are in a separate workbook.
我正在尝试使用 vlookup 函数在单独工作簿中的多个工作表中搜索值。
I am trying to use the IfError function with the vlookup but I am not formatting it correctly.
我试图在 vlookup 中使用 IfError 函数，但我没有正确格式化它。
Sub CommandButton1_Click()
Dim item As String
item = 8
Dim brange, rbrange, jrange, cdrange, cprange As range
Set brange = Workbooks("Library_Database.xlsx")_
.Worksheets("BOOKS").range("A2:H51")
Set rbrange = Workbooks("Library_Database.xlsx")_
.Worksheets("REFERENCE BOOKS").range("A2:H51")
Set jrange = Workbooks("Library_Database.xlsx")_
.Worksheets("JOURNALS").range("A2:H51")
Set cdrange = Workbooks("Library_Database.xlsx")_
.Worksheets("CDS").range("A2:H51")
Set cprange = Workbooks("Library_Database.xlsx")_
.Worksheets("CONFERENCE PROCEEDINGS").range("A2:H51")
Dim title As String
title = IfError(VLookup(item, brange, 2, False), _
IfError(VLookup(item, rbrange, 2, False), _
IfError(VLookup(item, jrange, 2, False), _
IfError(VLookup(item, cdrange, 2, False), _
IfError(VLookup(item, cprange, 2, False), "")))))
End Sub
回答by Niclas
Try this. You need to work with WorksheetFunctionwhen using Iferrorand you need to use Application.VLookup(read here: http://dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/).
Also VBA VLookupis sensitive to datatypes, so when you define item as stringit will not work. Instead you should use Variantto be covered in.Dim item As Variant
尝试这个。您WorksheetFunction在使用时Iferror需要使用并且需要使用Application.VLookup（阅读此处：http: //dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/）。
也VBA VLookup对数据类型敏感，因此当您定义item as string它时将不起作用。相反，您应该使用Variant被覆盖。Dim item As Variant
Also you need to define these ranges as Range. With your current code, you only define cprangeas a actual range.
Use this instead:
Dim brange As Range, rbrange As Range, jrange As Range, cdrange As Range, cprange As Range
您还需要将这些范围定义为Range. 使用您当前的代码，您只能定义cprange为实际范围。
改用这个：
Dim brange As Range, rbrange As Range, jrange As Range, cdrange As Range, cprange As Range
title = Application.WorksheetFunction.IfError(Application.VLookup(item, brange, 2, False), _
Application.WorksheetFunction.IfError(Application.VLookup(item, rbrange, 2, False), _
Application.WorksheetFunction.IfError(Application.VLookup(item, jrange, 2, False), _
Application.WorksheetFunction.IfError(Application.VLookup(item, cdrange, 2, False), _
Application.WorksheetFunction.IfError(Application.VLookup(item, cprange, 2, False), "")))))
In generel, it is good practice to use Option Explicit. Put this at the absolute first line in your module (outside your procedure).
一般来说，使用Option Explicit. 把它放在你的模块中的绝对第一行（在你的程序之外）。
