Unable to get the Match Property of the WorksheetFunction class error

anupkumar

New Member
Joined
Mar 4, 2016
Messages
3
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


Code:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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

New Member
Joined
Mar 4, 2016
Messages
3
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

New Member
Joined
Mar 4, 2016
Messages
3
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,902
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.

Code:
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
 
Last edited:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,043
Office Version
  1. 365
Platform
  1. Windows
Also I am now getting Compile error, Type Mismatch error for TargetEmployee is 0 statement...

Sorry, my typing mistake. It should have been:

Code:
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:

Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,418
Messages
5,596,028
Members
414,039
Latest member
southike

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top