if statement for each row and store the value in array

[may2051]

i'm very new to vba and i was wondering if someone can help out!

i'm trying to have if statement go through each row if condition meets, store the other column value in array so i can retrieve the values later on.

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]red[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]red[/TD]
[TD]orange[/TD]
[TD]purple[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]yellow[/TD]
[TD]blue[/TD]
[TD]orange[/TD]
[/TR]
</tbody>[/TABLE]

so for example, i want the if statement to go through the first row and if it doesn't contain 'purple' then store the value '1' in array and do the same thing for row 2, 3, and so on. in the end the i want to list out the values that were stored in the array...

is there any way to do this simply on excel? or on vba possible?

thank you for your help!

 

[My Aswer Is This]

So you want to search for the value Purple in row(1).

If the value is not purple you want to store the value in a array and then do what with the results of the array?

And you said start in row 1 and continue on to Row(2) and Row(3) and how do we know when to stop?

And what columns are we looking in?

Is it column A to G

And do we stop when we see no more values in column A

 

[may2051]

Apologize for the lack of information.

There are fixed number of rows and columns. so in this case it would be 4 columns and 3 rows. I want to search for a specific word (a word set by the user) for example 'purple' and look through the column B, C, D and see if the word 'purple' is contained in any of the column. if not, store the column A value into the array.

I want to repeat this for each row(1), (2), and (3), and in the end list out the values that were stored in the array in a cell. (So finding any rows that doesn't contain the word that i'm looking for, and listing out the first column values for all the rows that meets the condition)

So you want to search for the value Purple in row(1).

If the value is not purple you want to store the value in a array and then do what with the results of the array?

And you said start in row 1 and continue on to Row(2) and Row(3) and how do we know when to stop?

And what columns are we looking in?

Is it column A to G

And do we stop when we see no more values in column A

 

[My Aswer Is This]

You said:

so in this case it would be 4 columns and 3 rows​

What might it be in some other case?
And how do you plan to tell the script what case we are dealing with.
And you said:

and listing out the first column values for all the rows that meets the condition)​

List them out where?

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you're after

Sub FillAry()
   Dim ary() As Variant
   Dim Srch As String
   Dim Fnd As Range
   Dim i As Long, j As Long
   
   Srch = InputBox("Please enter value to search for")
   If Len(Srch) = 0 Then Exit Sub
   For i = 1 To 3
      Set Fnd = Rows(i).Find(Srch, , , xlWhole, , , False, , False)
      If Fnd Is Nothing Then
         ReDim Preserve ary(0 To j)
         ary(j) = Range("A" & i).Value
         j = j + 1
      End If
   Next i
   Range("O1").Value = Join(ary, ", ")
End Sub