Unable to get the Match Property of the WorksheetFunction class error

[anupkumar]

Hello Guys,

I am currently working on a submit macro option which should update the start and end data for an employee as per the process mentioned:

In sheet "Data_Entry_sheet" user enters the following details
1. Selects Employee name from dropdown menu, cell N6
2. Fill Employee start date in cell N8
3. Fill Employee end date in cell N9
4. Select Employee leaving status from drop down list in cell N10

Now after user enters any one or all details in sheet "Data_Entry_Sheet" and clicks submit button, macro assigned should pickup the name mentioned in point 1, search for the name in the list present in column C of Employee_data sheet. On finding the correct name, system will populate the value mentioned in point 2, 3, in corresponding columns in sheet "Data_Entry_Sheet"

For point 4, again macro should go to sheet "Employee_Status", search for name selected in point 1 and search in column B of "Employee_Status" sheet and on finding the correct entry update the details selected in point 4 for column F of corresponding name row in "Employee_Status" sheet. This part I have not mentioned in the code till now.

At the end macro should clear values filled up in point 1,2,3 and 4.

But at getting Worksheet funtion error for this particular statement...
TargetEmployee = WorksheetFunction.Match(TargetID, NameIDRange, 0)

Any suggestion guys how to correct this?

Thanks

Sub SubmitDate()


Dim TargetID As String


    TargetID = Sheets("Data_Entry_Sheet").Range("N6").Value


Dim NameIDRange As Range


    Set NameIDRange = Sheets("Employee_Data").Range("$C$2:$C$50000")
    
Dim ActualStartRange As Range


    Set ActualStartRange = Sheets("Phase_1_Data").Range("$M$2:$M$50000")
    
Dim ActualEndRange As Range


    Set ActualEndRange = Sheets("Employee_Data").Range("$L$2:$L$50000")


Dim TargetEmployee As Integer

[COLOR=#ff8c00][B]    TargetEmployee = WorksheetFunction.Match(TargetID, NameIDRange, 0)[/B][/COLOR]
    
 Dim ActualStartInput As Date


    ActualStartInput = Sheets("Data_Entry_Sheet").Range("N8").Value


        If ActualStartInput > 0 Then
        
        ActualStartRange.Item(TargetEmployee) = ActualStartInput
                
        End If


Dim ActualEndInput As Date


    ActualEndInput = Sheets("Data_Entry_Sheet").Range("N9").Value


        If ActualEndInput > 0 Then
        
        ActualEndRange.Item(TargetEmployee) = ActualEndInput
                
        End If
                
Sheets("Data_Entry_Sheet").Range("$N$6:$N$10").ClearContents
    
End Sub

 

[StephenCrump]

The error simply means that TargetID (=Sheets("Data_Entry_Sheet").Range("N6").Value) can't be found in NameIDRange (=Sheets("Employee_Data").Range("$C$2:$C$50000")).

Ideally your code should allow for this possibility:

TargetID = Sheets("Data_Entry_Sheet").Range("N6").Value
Set NameIDRange = Sheets("Employee_Data").Range("$C$2:$C$50000")

On Error Resume Next
TargetEmployee = WorksheetFunction.Match(TargetID, NameIDRange, 0)
On Error GoTo 0
If TargetEmployee Is 0 Then
    'No match found
    'Do something
Else
    'do other stuff
End If

Also, given you're searching 50,000 rows, you should declare TargetEmployee as Long, not Integer.

 

[anupkumar]

Hello Stephen,

Thanks for replying. I changed the declaration to long. But I dont understand why isnt the program picking up the name? It's there in the list as even vlookup shows it and separate match statement in a cell gives me row no. 43, but the program is unable to search for the value.

=Vlookup(N6,Employee_Data!$C$2:$C$50000,15,0)= 16.12.2015
=MATCH(N6,Employee_Data!$C$2:$C$50000,0) = 43

Also I am now getting Compile error, Type Mismatch error for TargetEmployee is 0 statement...

 

[anupkumar]

It's =Vlookup(N6,Employee_Data!$C$2:$N$50000,15,0)= 16.12.2015

Forum doesnt support modify last post so I am posting again..Sorry for the typo.

 

[mikerickson]

Application.Match works differently than WorksheetFunction.Match.

WorksheetFunction.Match will return a run-time error if no match is found.
Application.Match will return an Error value if no match is found.

Dim FoundPlace as [B]Variant[/B]

FoundPlace = Application.Match("something", someRange,0)

If IsError(FoundPlace) then
    MsgBox "something not found"
Else
    MsgBox "something is in location " & FoundPlace
End If

 

[StephenCrump]

Also I am now getting Compile error, Type Mismatch error for TargetEmployee is 0 statement...

Sorry, my typing mistake. It should have been:

If TargetEmployee [COLOR=#ff0000][B]=[/B][/COLOR] 0 Then

mikerickson has given you another way your code can handle MATCH errors. But your problem is that you think you have a match, and your code is not finding it correctly.

If in Excel: =MATCH(Data_Entry_Sheet!N6,Employee_Data!$C$2:$C$50000,0) = 43

then in VBA, TargetEmployee below should also be 43:

TargetID = Sheets("Data_Entry_Sheet").Range("N6").Value
Set NameIDRange = Sheets("Employee_Data").Range("$C$2:$C$50000")
TargetEmployee = WorksheetFunction.Match(TargetID, NameIDRange, 0)

But ... your formula has =MATCH(N6,Employee_Data!$C$2:$C$50000,0) instead, i.e. no worksheet qualifier on the N6.
Is this formula being used within the 'Data_Entry_Sheet' worksheet?