Finally, with some help from the good folks over in the microsoft.public.access.modulesdaovba newsgroup, I was able to complete my code to search an Access database for some information stored in an Excel worksheet.
Counting Unique Occurrences in an Excel Spreadsheet
This formula, entered as an array (Ctrl+Shift+Enter) in a single cell, will show you if there any duplicate entries in a given range (in this case, A1:C100). It is wrapped in an IF function to provide a friendly message; a kind of in-cell error handling, if you will. I usually do this when I know others are going to use the formula, to make it easy for them to understand the output. Otherwise it just gives you the number of unique entries which some people may not know what to do with.
Continue Reading »
Formatting Zip+4 Codes
If you have a column of zip+4, this macro will remove the suffix. Simply highlight the cells in question. It will skip any zip codes that don't have the suffix. It uses the Left$ string function which is more efficient than the standard Left() and always returns the first 5 characters in the cell.
Follow Me