Excel VBA：无法获取 WorksheetFunction 类的 Match 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16102122/
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 VBA: Unable to get the Match property of the WorksheetFunction class
提问by Sam WB
I'm trying to do a match search between two workbooks to see if a name entered in Wbook1 is in column A of Wbook2. For example... I have "name1" in cell D4 of workbook1... I then want the macro to search column A of workbook2 to find where "name1" is. I'm not worried about the name not existing on workbook2 as it should always be there.
我正在尝试在两个工作簿之间进行匹配搜索，以查看在 Wbook1 中输入的名称是否在 Wbook2 的 A 列中。例如......我在workbook1的单元格D4中有“name1”......然后我希望宏搜索workbook2的A列以找到“name1”的位置。我不担心 workbook2 上不存在的名称，因为它应该始终存在。
Using Excel 2007, code is:
使用 Excel 2007，代码为：
Sub ViewData()
Dim xlo As New Excel.Application
Dim xlw As New Excel.Workbook
Dim xlz As String
Dim result As Double
Dim SalesExec As String
SalesExec = Range("d4").Value 'D4 contains the name from workbook1 I want to search for
xlz = Range("y1").value 'This cell contains the file path for workbook 2
Set xlw = xlo.Workbooks.Open(xlz) 'Path is correct as the desired workbook does open
result = Application.WorksheetFunction.Match(SalesExec, xlo.Worksheets("Data").Range("A:A"), 0) 'Data is the sheet in workbook2 containing the list of names
Range("Q14").value = result
xlw.Save
xlw.Close
Set xlo = Nothing
Set xlw = Nothing
End Sub
If I remove .WorksheetFunction, I get an 'object or application defined error'. As the code stands, I get the 'unable to get the match property of the worksheetfunction class' error and I'm not sure why.
如果删除 .WorksheetFunction，则会收到“对象或应用程序定义错误”。正如代码所示，我收到“无法获取工作表函数类的匹配属性”错误，我不确定为什么。
Any help would be much appreciated. Thanks!
任何帮助将非常感激。谢谢！
采纳答案by Siddharth Rout
Try this. I have commented the code so you will not face a problem understanding it.
尝试这个。我已经对代码进行了注释，因此您不会在理解它时遇到问题。
Sub ViewData()
Dim xlo As New Excel.Application
Dim xlw As New Excel.Workbook
Dim xlz As String
Dim result As Double
Dim LRow As Long
Dim SalesExec As String
SalesExec = Range("d4").Value
xlz = Range("y1").Value
Set xlw = xlo.Workbooks.Open(xlz)
With xlw.Worksheets("Sheet1")
'~~> Find the last row cause Range("A:A") in match will give error
LRow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
xlo.Visible = True
'~~> Result is double so ensure that whatever you are trying to find is as Double
'~~> Also It should be xlw.Worksheets("Data") and not xlo.Worksheets("Data")
result = Application.WorksheetFunction.Match(SalesExec, xlw.Worksheets("Data").Range("A1:A" & LRow), 0)
Range("Q14").Value = result
xlw.Save
xlw.Close
Set xlo = Nothing
Set xlw = Nothing
End Sub
