Macro to search cell for a name from a list, then use 1 of 2 formulas to calculate

[Hobalong]

Hello, let me start off by saying that I am relatively new to VBA and macros in excel. My problem is this, I have a list of suppliers and shipping information, and I need to calculate if their shipments are on time using one of these two macros I've made;

=IF(U2>14,"Early",IF(U2<-7,"Late","On-Time"))
=IF(U3>7,"Early",IF(U3<0, "Late", "On-Time"))

The first macro is for our international suppliers, they are allowed up to 14 days early and 7 days late to count as the shipment being on time, and for suppliers in the states it is 7 days early allowed and 0 days late. What I need to figure out in a macro or VBA is to have it searched the field that contains the supplier name, and if it returns being 1 of the 5 international suppliers (I would have a list of the 5 int. suppliers in the macro somewhere) it would use the first equation to calculate it being on time or not.

I have attached a sample spreadsheet below, the supplier names in column B are examples of either US suppliers (US1, US2 ect.) or INT1-INT5 for the international suppliers. The blank spacing in between all the columns will be filled in later with other information and can be ignored. The "Status" Column is the field that uses either one of the above macros, and it references the column "Days Early / Late (-)" which is calculated from the "Planned Delivery date"-"Receipt Date"(date we receive the shipment).

If anyone may have any ideas on how I can be able to do this I would be very thankful.

[stnkynts]

If your supplier name in column B is going to be exactly (US1, US2, etc) and (INT1 - INT5) as indicated then this should work for you:

Please Login or Register  to view this content.

[ragulduy]

As stnkynts says, because you autfill the formula down the table, then you need to include the if statement to see if the supplier is international or not in the function that you paste down the table. Assuming that your supplier names are not US1, US2, INT1, INT2 etc. then I would suggest using a lookup table to do this, have a table with the supplier names in one column and INT or US in the next column. Then use the Vlookup function to determine which type the supplier is.

If you want to include it in the macro, you would need to have a for loop to populate the table and then put an if statement in the macro.

[Hobalong]

Sorry miss communication on my part, the names I gave above were just examples. Would I make a If statement similar to yours but for the five international suppliers I include their names? The five international supplier names are "DONGGUAN QINGXI LIAN", "WINTAGE ENGINEERS &", "GNOTEC MEFA AB", "PT TEXMACO PERKASA", and "IMPERIAL AUTO INDUST". How would I go about making an IF then else for these five names, and if it doesn't return being those 5 international sup. that it uses the second formula?

[ragulduy]

Either make a lookup table as suggested or type it longhand into the formula but it becomes a bit unreadable:

Please Login or Register  to view this content.

[Hobalong]

Thank you both, I was able to solve my problem.