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

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,

Hi Kathy,

It would be helpful to see your code.


---
Regards,
Norman



<kathy.aubin@gmail.com> wrote in message
news:1149634894.488779.67220@g10g2000cwb.googlegroups.com...
> 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,
>

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

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


---
Regards,
Norman



<kathy.aubin@gmail.com> wrote in message
news:1149637400.345269.278190@g10g2000cwb.googlegroups.com...
> 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
>