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 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?
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