Multiple Dictionaries Or One Dictionary

Hi all,

Using Excel 2007.

I have 4 lists Parts, Category, Country, Class
I need to load a string, split the substring into array, loop the array to search dictionary(ies) to assign each element to the correct array.

What would be best approach in term of the dictionary(ies)

Thx
w

Would be better to upload a wb and to show your expected results.

Thanks Mike,

Can I add a dictionary to an array
I'm getting a subscript out of range error
Seem like the code should work

Thanks
w

Please Login or Register  to view this content.

Give this a try

Please Login or Register  to view this content.

Thanks Mike,

That does not appear to add multiple dictionaries, which is fine - except, how do I know the class of an item if I do not know which dictionary it cam from?
My thought was

• Grab element from array avItem(j,i)
  Search dic(1) if found then item is in Class Country
  Assign to avCountry()
  Else search dic(2)
  If found then item is in class Customer
  Assign to avCustomer()
  Else search dic(3)
  If found then item is in class Part
  Assign to avPart()
  Else search dic(4)
  If found then item is in class Date
  Assign to avDate()
  Else
  Not found print avItem(j,i) #FileNum

Is that doable?
Is there a better way?
thx
w

Thanks Jindon,

It will take me some time to digest your code.
I've used VBA for awhile, I've never used the dictionary object.

thx
w

Give you the idea.

Please Login or Register  to view this content.

So, Main dictionary holds worksheet.names and each item holds its own Dictionary.