Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Filter Inactive status based on Leave Date

0

I have difficulty doing:  

1. If the user "Leave date" 13-Jul-22", "Payroll Status" is "Inactive".  The result in the "Active" field is "No" and "custom_field: Last day of work" is 13/07/2022  

2.  If the user "Leave date" 31-Aug-23", "Payroll Status" is "Inactive".  The result in the "Active" field is "Yes" and "custom_field: Last day of work" is 31/08/2022   Herewith I attach the latest file for your reference.    

I'm stuck with this and need your guidance.

Answer
Discuss

Answers

0
Selected Answer

Hi Nabila

I think I understand your question (but the title "Filter..." confused me, as did your file which did not include the sample records you refer to).

Your existing sub EditData currrently filters out any records where Active Status (in column G, =9) is "Inactive", via the line in bold in this portion:

With RangeToFilter
    .AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
    Criteria2:="=-"
    .AutoFilter Field:=9, Criteria1:="=Active"
    .AutoFilter Field:=14, Criteria1:="=Inpat"
End With

Just remove that line, comment it out like this:

With RangeToFilter
    .AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
    Criteria2:="=-"
'    .AutoFilter Field:=9, Criteria1:="=Active" ' allow InActive records
    .AutoFilter Field:=14, Criteria1:="=Inpat"
End With

to get mixed records (in both cases) or change it to:

With RangeToFilter
    .AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
    Criteria2:="=-"
    .AutoFilter Field:=9, Criteria1:="=Inactive" ' only InActive records
    .AutoFilter Field:=14, Criteria1:="=Inpat"
End With

to get only Inactive records. You should then get the dates and "No" entries as you describe.

REVISION:

Further to your clarification (or my oversight). the attached revised file only filters only for Inpat by commenting out the TWO filter lines below, keeping only those in bold:

With RangeToFilter
'    .AutoFilter Field:=10, Criteria1:=">=" & CriteriaWildCard, Operator:=xlOr, _
    Criteria2:="=-"
'    .AutoFilter Field:=9, Criteria1:="=Active" ' allows Inactive records too
    .AutoFilter Field:=14, Criteria1:="=Inpat"
End With

Then records with a leaving date do appear but this line is added to the loop which sets the Status (column 6) and writes "No" if the status is inactive but the Leaving Date is in the future:

For k = 2 To nwsLastRow
    If nws.Cells(k, 6) = "Active" Then
        nws.Cells(k, 6) = "Yes"
        Else: nws.Cells(k, 6) = "No"
        ' Overwrite Status if leaving date is in the future
        If nws.Cells(k, 14).Value > Date Then nws.Cells(k, 6) = "Yes"
End Ife

If you want to change and select how records are filtered, you might consider creating a VBA UserForm (e.g. with "radio buttons" for Active and Inactive)- Don has a few lessons in the subsection "UserForms in Excel" within the Tutorials page, hyperlinked in the green banner above.

Hope this fixes it for you.

Discuss

Discussion

Hi John, 
What I mean is, that the record has user Leave date = 13-Jul-22 and the Payroll status = Inactive. The end result will be, Status = No and show the user's last day in column N (custom_field: Last day of work) = 13/07/2022. For another user,  Leave date = 31-Aug-23, and the Payroll status = Inactive. The end result will be, Status = Yes and show the user's last day in column N (custom_field: Last day of work) = 31/08/2023.


nabila (rep: 8) Aug 29, '22 at 12:26 pm
Nabila, sorry about the (now deleted) comment. Please see my revised answer / file.
John_Ru (rep: 6102) Aug 29, '22 at 1:24 pm
Hi John, 
Your code is working! 
You always respond quickly and never disappoint the members of this forum. Are you working as a programmer? Hopefully, this forum will exist until any time to help other members and the new generation. Thanks again, John.
nabila (rep: 8) Aug 30, '22 at 4:37 am
Glad it worked and thanks for selecting my answer, Nabila.

I'm not working at all (and never as a programner) but like solving problems and have some time to spare to help here.

I too hope that the Forum lasts and that Don keeps giving us his great tutorials!
John_Ru (rep: 6102) Aug 30, '22 at 6:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login