loop through a column and match against a list or array

[30percent]

Hi,

what is the easiest and cleanest code to loop through a column and if the cell value matches a value in a predetermined list then do so such and such?

thanks

 

[Joe4]

It depends.
What exactly is it that you want to do?

 

[30percent]

hope this explains what i would like to do:

fruits = "apple, banana, watermelon, tangerine"
list_Fruits = Split(stream, ";")

For i = 2 To finalRow_Paste_tab

if ThisWorkbook.Worksheets("Paste").Range("E" & i).Value equal to a value in the list then
  do something
end if

Next

 

[JEC]

Using a dictionary first to read your data. Aterwards you can easily check if the values do exist in the dictionary

Sub jec()
 Dim dic, it, ar, j As Long
 Set dic = CreateObject("scripting.dictionary")
 
 For Each it In Array("apple", "banana", "watermelon", "tangerine")
   dic(it) = Empty
 Next
 
 ar = Array("melon", "watermelon", "pineapple", "apple")
 
 For j = 0 To UBound(ar)
   If dic.exists(ar(j)) Then
     Debug.Print ar(j) & " in list"
   Else
     Debug.Print ar(j) & " not in list"
   End If
 Next
End Sub

 

[Joe4]

Well, not really. I wanted to know exactly what "do something" is - updating cells? posting a message? etc?

However, if the values in the cells match exactly one of those options (i.e. "strawberry"), then you do not need to split the string of values at all.
Just use the InStr function to see if it exists in the string, i.e.

fruits = "apple, banana, watermelon, tangerine"

For i = 2 To finalRow_Paste_tab
   
    If InStr(fruits, Worksheets("Paste").Range("E" & i)) Then
        'do something
    End If

Next i

The nice thing about this option is there is only one loop!

 

[JEC]

But that is just a sample string There probably is more data

 

[Joe4]

But that is just a sample string There probably is more data

It makes no difference - they can make it as long as the want with as many items in the string as they want.
As long as the values in the cells are singular items of that list (like "apple") and not mixed with other words (in the same cell) not on the list, it will still work.

I guess the only time you could have potential issues if you have partial words, i.e. the word in the cell is "apple" and apple is not in there word list, but "pineapple" is.
However, you could get around that by doing something like this: adding a "," to the beginning and end of the data list, and use this updated code:

fruits = ",pineapple,banana,watermelon,tangerine,"

For i = 2 To finalRow_Paste_tab
   
    If InStr(fruits, "," & Worksheets("Paste").Range("E" & i) & ",") Then
        'do something
        MsgBox i
    End If

Next i

 

[JEC]

Yes but the dictionary is far more efficiënt on larger datasets.

 

[Joe4]

Yes but the dictionary is far more efficiënt on larger datasets.

You may want to tailor your code more to their specific scenario, i.e. you aren't even referencing the cells on their sheet, you are just comparing two lists you have directly in your VBA code.

 

[JEC]

I know, that is just an example;

- Dictionary lookups have an average time complexity of O(1). This means that checking for the existence of an element is done in constant time, regardless of the size of the dictionary.

- Searching for a substring within a string using InStr has a time complexity of O(n×m), where n is the length of the string and m is the length of the substring. This approach becomes increasingly slower as the dataset grows.