asked on

Adding criteria to Excel VBA so blank rows are not calculated

The following VBA solution from this forum has been working great. I just need to add the following criteria to stop the calculation from happening on blank rows:

If Column A = "" then Column J (Expiry Issue) = ""

The existing code is below. Additionally, I have attached a sample Excel file as well as a screen capture depicting what I am looking for.

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)
Select Case Cells(rng.Row, "A")
    Case "AIP_ENR", "AIP_AD", "AIP_GEN"
        expiryIssue = ""
        Exit Function
End Select
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_Add_Criteria_Blank_Rows.xlsm
Additional_criteria_Mar29.png

Martin Liss

The expiryissue function contains this code

            If dte1 = "" Then
                If dte2 < Date Then expiryIssue = "Issue"
            Else
                If dte2 < Date Then expiryIssue = "Issue"
            End If

Open in new window

which looks like a mistake because the 'If' and the 'Else' do the same thing.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Hi Martin,

Thanks for your quick turnaround - your solution works great! :-) And thanks for pointing out the code issue - this was my error in trying to tweak the code myself at one point.

Andrea

Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
       Experts Exchange Top Expert VBA 2018, 2019
       Experts Exchange Distinguished Expert in Excel 2018