We help IT Professionals succeed at work.
Get Started

Adding additional criteria to VBA solution in Excel

61 Views
Last Modified: 2020-03-01
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

Open in new window


Thanks!
Andrea
EE_Expiry_Exclude_Sections.xlsm
Comment
Watch Question
Keep everyone healthy; Wear a mask - Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2020
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE