Avatar of Andreamary
Andreamary
 asked on

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

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
Microsoft OfficeVBAMicrosoft Excel

Avatar of undefined
Last Comment
Andreamary

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andreamary

ASKER
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
SOLUTION
Saqib Husain

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Andreamary

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck