Excel VBA: Unable to get the Match property of the WorksheetFunction class
17,500
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
Author by
Sam WB
Updated on June 04, 2022Comments
-
Sam WB almost 2 years
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.
Using Excel 2007, code is:
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.
Any help would be much appreciated. Thanks!
-
Sam WB about 11 yearsExcellent. This works. Perhaps the error was related to not finding the last row? Either way, thanks!