problem retrieving string in a collection

andreagiugio

New Member
Joined
Oct 22, 2015
Messages
30
Hi all,

I put in a oErrors collection all values stored in column A starting from cell A2

Code:
Dim oErrors As New Collection
    On Error Resume Next
    lastrow_errors = ActiveSheet.UsedRange.Rows.count
    
    
      For Each cell2 In ActiveWorkbook.Sheets("generale").Range("A2:A" & lastrow_errors)

            oErrors.Add cell2.Value, Key:=cell2.Value

        Next cell2


Then, for each cells in an other range (that are strings) I need to check if there is a string in the collection that contains that string. Thestring found in Errors collection then will be my new cell.value

Code:
                For i = 1 To oErrors.count
                    If oErrors(i).Contains(cell.Value) Then
                        cella2 = oErrors(i)
                        Exit For
                    End If
                Next
cell.Value = cella2

For example:

I have cell.value that is string "Fornit*esistente"
In the oErrors collection I have a string that is "Fornitore inesistente".
Right now I am getting ALWAYS the first string present in Errors collection, that it is "Codice prestazione & non trovato".
It is getting always that first string and then go to "Exit For" for each cell.

Could you help?
Thanks!

<tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
could you post more code - for the second part?
Tell me if I'm wrong but it looks to me like a partial match with a wildcard. Wouldn't it be better to use LIKE instead of Contains.
And btw what is Contains?
 
Last edited:
Upvote 0
could you post more code - for the second part?
Tell me if I'm wrong but it looks to me like a partial match with a wildcard. Wouldn't it be better to use LIKE instead of Contains.
And btw what is Contains?

Hi!
I would post it but it will create a lot of confusion because there is a lot of code before get the string "Fornit*esistente" that after I have to look inside oErrors collection.
I have summarized the code to the maximum.

In the example I have provided I have verified that oErrors(i) with i = 1 is the string "Codice prestazione & non trovato" but cell.value "Fornit*esistente". So the string I am willing to retrieve from oErrors should be "Fornitore inesistente".

Code:
                For i = 1 To oErrors.count
                    If oErrors(i).Contains(cell.Value) Then
                        cella2 = oErrors(i)
                        Exit For
                    End If
                Next



My goal is to verify if the string "Fornit*esistente" is contained in oErrors collection.
It should be as in oErrors collection I have "Fornitore inesistente".
I will give it a try with LIKE instead of Contains.

Thanks.
 
Upvote 0
Hi!
I would post it but it will create a lot of confusion because there is a lot of code before get the string "Fornit*esistente" that after I have to look inside oErrors collection.
I have summarized the code to the maximum.

In the example I have provided I have verified that oErrors(i) with i = 1 is the string "Codice prestazione & non trovato" but cell.value "Fornit*esistente". So the string I am willing to retrieve from oErrors should be "Fornitore inesistente".

Code:
                For i = 1 To oErrors.count
                    If oErrors(i).Contains(cell.Value) Then
                        cella2 = oErrors(i)
                        Exit For
                    End If
                Next



My goal is to verify if the string "Fornit*esistente" is contained in oErrors collection.
It should be as in oErrors collection I have "Fornitore inesistente".
I will give it a try with LIKE instead of Contains.

Thanks.


Problem solved substituting Contains with LIKE.
Thanks bobsan42!
 
Upvote 0
Rich (BB code):
                For i = 1 To oErrors.count
                    If oErrors(i).Contains(cell.Value) Then
                        cella2 = oErrors(i)
                        Exit For
                    End If
                Next
Should really be:
Rich (BB code):
                For Each er in oErrors
                    If er.Contains(cell.Value) Then
                        cella2 = er
                        Exit For
                    End If
                Next er

You don't want to loop through collections by their index, they aren't arrays and it's incredibly slow.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top