(Solved) – Is there a way to perform multiple checks in Excel using VBA

(solved)-–-is-there-a-way-to-perform-multiple-checks-in-excel-using-vba

I’m looking to check values over a range of multiple cells altering the data in my current cell dependant on the result. My below method works but poorly. My goal is a setup with multiple checks (one for each criteria) or one function that checks each criteria.

The below image shows the current setup that I’ve done in each cell.
In column AT I have the following formula:

=IFERROR(IF(ISNUMBER(SEARCH("Zero Ticket Booking",AS126)),"Zero Ticket Booking",IF(ISNUMBER(SEARCH("Refunded Booking",AS126)),"Refunded Booking",IF(ISNUMBER(SEARCH("Automatic Cancellation",AS126)),"Automatic Cancellation",IF(ISNUMBER(SEARCH("Nagios Booking",AS126)),"Nagios Booking",IF(AND(ISNUMBER(SEARCH("Theatre",AO126)),(ISBLANK(A126))),"No Venue Ref - Theatre",IF(AND(ISNUMBER(SEARCH("Event",AO126)),ISBLANK(A126)),"No Venue Ref - Event", IF(ISNUMBER(SEARCH("Event",AO126)),VLOOKUP(A126,Confirmed,40,FALSE),FALSE))))))),"Didn't Rebook")

This formula checks the cells previous to it on the same row to check if the last check performed was able to identify the order attribute.

enter image description here
As Brief background to the check I need to do

  • Automatic Cancellation –> Checks whether a cell’s text is “AUTO”
  • Refunded Booking –> Checks whether a cell’s value is above 1 AND cell on same row
  • Nagios Booking –> Checks whether a cell’s value in column B (same row) is 0
  • Zero Ticket Booking –> Checks whether a cell’s value in column U (same row) is 0

A final check that hasn’t been setup yet is my most troublesome. I want to check the date an order was confirmed against the date it was cancelled (which is possible). It is possible that there is no cancelled date or that there is only a cancelled date.

Because of the fact there is potential for there to be no confirmed/cancelled booking with the same Venue Reference(UNIQUE ID), I have attempted this but not been able.

=IF(OR(IFERROR(VLOOKUP(A2,Confirmed,33,FALSE)=0,0),IFERROR(VLOOKUP(A2,Cancelled,33,FALSE)=0,0)),1,2)

Or this

=IF(AND(VLOOKUP(A4,Confirmed,33,FALSE)>0,(VLOOKUP(A4,Cancelled,33,FALSE)> 0)),VLOOKUP(A4,Confirmed,33,FALSE)>VLOOKUP(A4,Cancelled,33,FALSE), IF(AND(VLOOKUP(A4,Confirmed,33,FALSE)<1,(VLOOKUP(A4,Cancelled,33,FALSE)> 0)),"NOT CONF/ YES CANC", IF(AND(VLOOKUP(A4,Confirmed,33,FALSE)>0,(VLOOKUP(A4,Cancelled,33,FALSE)>1)),"YES CONF/NO CANC",2)))

Let me know what detail is required for a better understanding of my answer. For instance the last formula has a vlookup and column_index(33) is the date.

Leave a Reply

Your email address will not be published. Required fields are marked *