Cthulhu does business with companies in the Midwest, selling them goating supplies. Most companies pay their bills on time, but some accounts are delinquent. Write an Excel VBA program that analyzes the data.

Cthulhu has given you a starting worksheet, with data on companies with delinquent accounts. Only delinquent accounts are in the file. The data is like this:


The first field is the name of the company. The second is the state the company is in. The third is the balance owed.

There might be errors in the data. Exclude records with errors from the analysis. Here are the rules:

  • Name: cannot be blank.
  • State: must be one of IA, IL, IN, MI, or OH. Allow for case differences, and extra spaces at the start and end. So " iA " is OK.
  • Balance: a number greater than zero.

Your worksheet should start like this:


Here's what the output should be:


The output shows the number of valid and invalid records, the total balances from each state, and the grand total. The totals only include data from valid records.

Cthulhu also wants to know the name of the company with the highest balance, and what that balance is. Why does it want to know? Best not to ask.


Wouldn't want to be them. Just sayin.

Upload your workbook. The usual standards apply.

Where referenced