troubleshooting Question

Adding additional criteria to VBA solution in Excel

Avatar of Andreamary
Andreamary asked on
Microsoft OfficeVBAMicrosoft Excel
3 Comments1 Solution62 ViewsLast Modified:
I realized that I need an additional criteria added to the great solution I received yesterday on this forum. I've been trying to solve it myself but don't have the skillset yet to solve it.

Sample of spreadsheet
I have attached a sample spreadsheet that shows the results I am looking for once the additional criteria is added.

The additional criteria is:
If Column A = "AIP_ENR" or "AIP_AD" or AIP_GEN" then Expiry Issue = ""

The current solution's VBA code is as follows:

Function expiryIssue(rng As Range) As String
Application.Volatile
Dim dte1 As Range
Dim dte2 As Range
Dim status As String
Set dte1 = rng.Cells(1, 1)
Set dte2 = rng.Cells(1, 2)
status = rng.Cells(1, 3)
If status <> "Expired" And status <> "Cancelled" And status <> "Replaced" Then
' If there is no Proposed or Approved expiry date
    If dte2 = "" And dte1 = "" Then
        expiryIssue = "Missing expiry dates"
    Else
    'If there is no Approved Expiry date and the Proposed Expiry date is within 60 days of todays date
        If dte2 = "" Then
            If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date"
        Else
        'If the Approved Expiry date is older than today's date
            If dte1 = "" Then
                If dte2 < Date Then expiryIssue = "Issue"
            Else
                If dte2 < Date Then expiryIssue = "Issue"
            End If
        End If
    End If
End If
End Function

Thanks!
Andrea
EE_Expiry_Exclude_Sections.xlsm
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros