vba Excel VLookup“需要对象”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21339710/
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
Excel VLookup "Object Required" Error
提问by jeremytripp
Here's another one of those "so simple it's maddening when it doesn't work" type questions.
这是另一个“如此简单，当它不起作用时令人发狂”类型的问题。
Got a macro below. It's based on an ActiveX Combo box which is linked to C3. When the user makes a selection, the macro is supposed to go find that selection on another worksheet, identify the isbn (in column 2), and return to the original worksheet to paste it in the appropriate cell (C4).
下面有一个宏。它基于链接到 C3 的 ActiveX 组合框。当用户进行选择时，宏应该在另一个工作表上找到该选择，识别 isbn（在第 2 列中），然后返回到原始工作表以将其粘贴到适当的单元格 (C4) 中。
Stepping through, it throws an "Object Required" error at isbn.Select.
逐步执行，它会在 isbn.Select 处引发“需要对象”错误。
I'm sure it's something easy. Always is. Can you give me hand?
我相信这很容易。永远是。你能把手给我吗？
Private Sub TitleSelection_Change()
Dim lookFor As Range
Dim rng As Range
Dim isbn As Variant
Set lookFor = Sheets("AAP Dashboard").Range("C3")
Set rng = Sheets("Books").Columns("A:I")
isbn = Application.VLookup(lookFor, rng, 2)
Application.ScreenUpdating = False
isbn.Select
Selection.Copy
Sheets("AAP Dashboard").Range("C4").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
采纳答案by Kelvin Dealca
You can't select isbn because it is variant object, it doesn't have that member.
你不能选择 isbn 因为它是变体对象，它没有那个成员。
Try:
尝试：
Dim lookFor As Variant
Dim rng As Range
Dim isbn As Variant
lookFor = Sheets("AAP Dashboard").Range("C3").Value
Set rng = Sheets("Books").Columns("A:I")
isbn = Application.WorksheetFunction.VLookup(lookFor, rng, 2, FALSE)
Application.ScreenUpdating = False
Sheets("AAP Dashboard").Range("C4").Value = isbn
Application.ScreenUpdating = True
I think you are missing an object between Application and Vlookup, add "WorksheetFunction". Also note that vlookup returns the value it finds in column 2 so no need to copy and paste. Finally note that Vlookup's first argument is a value not a range object so I changed lookFor to Variant
我认为您在 Application 和 Vlookup 之间缺少一个对象，请添加“WorksheetFunction”。另请注意， vlookup 返回它在第 2 列中找到的值，因此无需复制和粘贴。最后请注意，Vlookup 的第一个参数是一个值而不是范围对象，所以我将 lookFor 更改为 Variant
回答by Dmitry Pavliv
You can slightly modify your code to get desired result:
您可以稍微修改代码以获得所需的结果：
Set lookFor = Sheets("AAP Dashboard").Range("C3")
Set Rng = Sheets("Books").Columns("A:I")
isbn = Application.Match(lookFor, Rng.Columns(1))
If Not IsError(isbn) Then
Rng.Cells(isbn, 1).Select
'your code here
End If
Btw, try to avoid Selectand Active...statements (how to avoid them)
顺便说一句，尽量避免Select和Active...陈述（如何避免它们）
