Excel VBA Search a Cell for a String in Array

Question

In excel,

I have a table with a bunch of data in it. A simplified version is below. I have one column with an Activity ID and a second column that contains a list of strings that designate that activity as something...

Activity - Factor

1 - Test, Fun, Trial

2 - Trial

3 - Test, Fun, Wrong

In VBA, I want to find each activity that contains Test, Right or Wrong.

So I was thinking a "For" statement that looks at each row of data in the table. Then compares it to an array that contains the strings (Test, Right, Wrong). I want it to then return the Activity name and the matching value in the array.

Dim FactorMatch as Variant
FactorMatch = Array ("Test", "Right", "Wrong")

For i = 2 to LastRow
   if sht.cells(i,"B") = value in array then....

Now I'm stuck. I want an if statement, that looks at the Activity cell and if it contains any string within FactorMatch then it saves the Activity ID and the matching string to another array. So my Array SavedData will look like this in the end

SavedData = 1 Test 3 Test, Wrong

Doug Coats · Accepted Answer · 2017-08-14 21:17:46Z

1

I think it would be easier to write it like this.

dim rng as range, rcell as range
dim arr(5000) as string
dim counter as long
counter = 0
set rng = thisworkbook.sheets("yoursheetname").Range("yourrange")
for each rcell in rng.cells
     IF LCase(Instr(1, rcell.value, "test")) >0 Or _
     LCase(Instr(1, rcell.value, "right")) >0 Or _
     lCase(Instr(1, rcell.value, "wrong")) >0 Then
          arr(counter) = rcell.offset(0, numbertoadjustfor).value
          counter = counter + 1
     end if
next rcell

answered Aug 14, 2017 at 21:17

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA Search a Cell for a String in Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related