We help IT Professionals succeed at work.

UsingVBA to populate results in column based on criteria in 3 other columns

Andreamary
Andreamary asked
on
I would like VBA code to identify expiry issues with a list of documents in a spreadsheet and populate the results in Column J, based on the following criteria.

I have attached a sample spreadsheet with the anticipated results entered manually in Column J.

NOTE: If there is a date in Column H, then any dates entered in Column G are ignored.

If Col I = "" or <> = "Expired" or <> = "Cancelled" or <> = "Replaced", and
Col G = "" or <> "", and
Col H <> "" and date > than TODAY, then
Col J = ""


If Col I = "" or <> = "Expired" or <> = "Cancelled" or <> = "Replaced", and
Col G = "" or <> "", and
Col H <> "" and date < than TODAY, then
Col J = "Issue"


If Col I = "" or <> = "Expired" or <> = "Cancelled" or <> = "Replaced", and
Col G <> "" and date < than TODAY + 60 days, and
Col H = "", and
Col J = "Review Proposed Expiry Date"


If Col I = "" or <> = "Expired" or <> = "Cancelled" or <> = "Replaced", and
Col H = "", and
Col G = "", then
Col J = "Missing Expiry Dates"

Thanks!
Andrea
EE_Expiry.xlsm
Comment
Watch Question

View Solutions Only

Engineer
CERTIFIED EXPERT
Commented:

Try this formula in J4


=expiryIssue(G4:I4)


With this code


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 dte2 = "" And dte1 = "" Then 
        expiryIssue = "Missing expiry dates" 
    Else 
        If dte2 = "" Then 
            If dte1 < Date + 60 Then expiryIssue = "Review proposed expiry date" 
        Else 
            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
Andreamary

Author

Commented:
Thanks very much, Syed, this is great! The one thing I am having difficulty with is taking your solution and applying it to my actual spreadsheet. I've been trying to figure out how to change the column/row references in the VBA but am not having success.

This is the list of differences between my sample spreadsheet that I provided and my actual spreadsheet:

Col G (proposed expiry date) is Col H in my actual spreadsheet
Col H (approved expiry date) is Col J in my actual spreadsheet
Col I (Status) is Col BD in my actual spreadsheet
Col J (Expiry Issue) is Col BE in my actual spreadsheet

Would you be able to provide me with the changes in the VBA so it can work in my actual spreadsheet?

Thanks!
Andrea
Saqib Husain, SyedEngineer
CERTIFIED EXPERT
Commented:

No change to VBA


Change 


=expiryIssue(G4:I4)


on the worksheet to your new range.

Andreamary

Author

Commented:
Hi Syed,

I was able to figure out how to change the references based on your solution, so now the code is working in my actual spreadsheet!

Thanks again...I'm very pleased with your solution. :-)

Cheers,
Andrea