How can I ignore value from a workbook that are not in my collection?

[kathy.aubin]

Hi,

I have created a collection in VB Excel.
My macro is reading a value in one cell (on each row) in a spreadsheet
and using the collection, it return a Name.
Sometime, the value that the macro took from the spreadsheet is not
found in the collection index so I get an error message and the macro
stopped.
But those missing value are volontary so I would like the macro to
ignore those and continue with the rest of the function.
Is there a way to do that?

Thanks,

 

[Norman Jones]

Hi Kathy,

It would be helpful to see your code.

 

[kathy.aubin]

Let's say I have the collection

Function InitializeCollectionAUMTeam(FundsCollection As Collection)

FundsCollection.Add "AA", "FundA
FundsCollection.Add "BB", "FundB"

End Function


In the main I have a function that goes in a spreasheet reading cell A
for one row at a time.
It takes the value of cell A and try to find it in the collection :
here, it looks at if the value of cell A is = "FundA" or ="FundB"
If it does, I don't have any problem.
But let's say cell A = FundC, the macro stopped with a message telling
me that this is not in my collection.
But the fact that FundC is not in the collection is something I want.
So I would like the macro to ignore values in cell A where it is not =
FundA or = FundB

My main function is just too long to post in here. I hope this is
enough to understand what is my problem.

Thanks

 

[Norman Jones]

Hi Kathy,

If your collection holds the cells (as range objects), perhaps you can use a
construct like:

If not myItem is nothing then

If the collection stores the cell values, perhaps try using an error handler

On Error Resume Nest