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