Excel VBA Compare Arrays

The following post will compare the contents of one array against the contents of another.  The use case revolves around taking a detail list and comparing each row against a master list, to make sure each row of the detail array exists.  If the detail record does not exist, the cell background color of the invalid comination(s) will be set to Aqua and a message box will appear, prompting the user to review.

Excel:  Version 2013

Excel VBA Compare Arrays

You have the following two tabs in an Excel Workbook

MasterList

Excel VBA Compare Arrays

Detail

Excel VBA Compare Arrays

 

The task is checking the each combination of “Product” and “Description” on the Detail tab exists on the MasterList tab.

Here is the code:

 

 

After running the code above, you will get the following:

 

Excel VBA Compare Arrays

 

If each combination on the detail list does indeed exist in the master list, you will get the following:

 

Excel VBA Compare Arrays

 

And there you have it!  Please modify the code above to suit your needs.

 

Here is the Excel Macro-Enabled Workbook for the above post:

Excel VBA Compare Arrays

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment