Using Arrays to validate code combinations and retrieve category, dynamically

[Kles]

I am posting on VB Forums to hear some ideas or a "best practices" approach to complete our objective. Please see attachment for further visual understanding and “In Development” macro.

Background:
We have three list of codes that when combined should equal one category. The list of codes are currently on three different sheets with the category listed as the header. The codes change periodically and we need a non-excel wiz to be able to manage the codes (but the current approach isn’t necessary) Please see attachement for further visual understanding and beg

Objective:
A macro that can retrieve the correct category based on these codes.

Requirements/Notes:
All code are always used. Codes for some categories are shared on one list and unique on another list. All combinations should be unique and therefore equal one category
The macro must be dynamic, as codes could be added & deleted.

Current Idea:
I am thinking about creating a series of arrays by category & code(s). Then using an IsInArray function coupled with some IfThen constructs to identify the Category. Ideally, I’d like to use an “Intersect” function to determine the category as I think this will yield a faster runtime.

Kles

example.zip

[sparbag]

Hello Kles,

Please do not take offense but I doubt anybody will open the file attached as the security risk in opening an unsigned xlsm is too high to justify. Can you please paste your code and images of the worksheets.

Kind regards

Steve

[westconn1]

whilst i would be prepared to open the file i can only open .xls as my version of excel is too old for these new files