MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Determine an element in a Collection ?

  Asked By: Ryan    Date: Feb 20    Category: MS Office    Views: 1741

Is there a better way to determine if a variable (number or range) is
inside a collection ? Of course, I can make an IF statement to
compare one by one, but that is too lengthy.

A collection can be an array or union of range or a New Collection,
but I don't know which one is the better choice.



1 Answer Found

Answer #1    Answered By: Betty White     Answered On: Feb 20

I would expect there would be little difference in looping through a New
Collection or Array. Probably a bit slower with a union of range  because of
the interaction with the spreadsheet.

However you could think about building an index to the range and then using
vlookup or match to search it. It would take longer for the setup but the
searches might be faster. It depends how dynamic the content is and how many
times you want to evaluate.

I am thinking, if it was a stock list which changed say 500 times a day and
you had to search it 10,000 times a day, you would rebuild the index if an
item reached nil or a nil item was replenished, but each search would just be
a single vlookup or match.

Didn't find what you were looking for? Find more on Determine an element in a Collection ? Or get search suggestion and latest updates.