Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Andreamary
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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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