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]

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.

I am not disputing any of that. I just don't know that the OP is going to be able to adapt what you posted to their situation if they do not already have a good working knowledge of building arrays and/or dictionaries. If they did, they probably would not be asking the question.

So you really answered a question somewhat similar to theirs, but not the actual question they asked!

 

[JEC]

Let's wait for him to answer. The adjustments which are needed are pretty clear I guess.

 

[30percent]

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!

I tried this sample code. However, depending on the cell matching which value in fruits - the value of: InStr(fruits, Worksheets("Paste").Range("E" & i)) varies from 1, 6, 20, 15.

And on occasion the cell value that is not even in fruits, the value of InStr(fruits, Worksheets("Paste").Range("E" & i)) is 22.

Trying to make sense of this and make it work.

 

[30percent]

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

thanks JEC, my dataset is relatively small - less than 5k rows.

 

[Joe4]

I tried this sample code. However, depending on the cell matching which value in fruits - the value of: InStr(fruits, Worksheets("Paste").Range("E" & i)) varies from 1, 6, 20, 15.

And on occasion the cell value that is not even in fruits, the value of InStr(fruits, Worksheets("Paste").Range("E" & i)) is 22.

Trying to make sense of this and make it work.

I am not really sure what you are saying. I think it is time to move off of generalities and move into specifics.
Can you show us a sample of your data and expected output (just being sure to remove any sensitive data first)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.