“Unable to get the Match property of the WorksheetFunction class”

[Marsmuncher]

Hello,

I am unable to get this code to work. What is happening is I am trying to make a String of Prod obtain data relevant to what is matched. I have two worksheets in the same workbook. One named "Database" and one named "Datasheet". I am trying to match the values of column B from "Database" with the data from column E from "Datasheet" by using the INDEX and MATCH functions in VBA. Below is my code:

Private Sub Rdson_Update_Click()
Dim Prod As String

'Calling Module 4
Call Rdson_Data

Prod = Application.WorksheetFunction.Index(Sheets("Database").Range("B:B"), Application.WorksheetFunction.Match(1, Sheets("Datasheet").Range("E:E"), 0), 0)

End Sub

However I always get one of two errors, one of them being “Unable to get the Match property of the WorksheetFunction class” and the other being "Application-defined or object-defined error"

The Call Rdson_Data is a module that brings the datasheet from another Excel file, and I have this working perfectly, so I know this is not the problem. The Data itself is package numbers i.e. LM339-10.

 

[RoryA]

The error means that the value 1 can't be found in column E on the Datasheet sheet.

 

[Norie]

Your Match function is looking for the value 1, is that right?

 

[Marsmuncher]

Ah, No it isn't, it appears I found my mistake, what I am trying to achieve is my match function to scan all the contents with column E from "Datasheet" and to then match it with all the contents from column B in in "Database"