filter out the array

**kaffal** · 03-28-2009, 08:26 PM

hi I have a array which take the value from the column J. izit possible to have a check in column B. If one of the value in column J was founded in column B(cell B3:B50), it will not put the value into the array.

ReDim List(z)
For a = 1 To z
List(a) = Cells(a, 10)
Next a

**shg** · 03-28-2009, 08:34 PM

You can use the Find method to try to locate a given value from col J in the range of interest in col B. If you find it, don't add it to the array.

**kaffal** · 03-28-2009, 09:01 PM

how to use the find method in my example? or is there any example for me to refer to ?

**ashish4422** · 03-29-2009, 02:06 PM

Hope this will help you

Private Sub CommandButton1_Click()

ReDim j(100)

Count = 0
l = 1
While Worksheets("Sheet1").Cells(l, 2) <> ""
l = l + 1
Count = Count + 1
Wend

k = 1
ce = 1
While Worksheets("Sheet1").Cells(k, 10) <> ""
k = k + 1
ce = ce + 1
Wend

For n = 1 To ce
    i = 1
    nonmatch = 0
    While Worksheets("Sheet1").Cells(i, 2).Value <> ""
         If (Worksheets("Sheet1").Cells(n, 10).Value <> Worksheets("Sheet1").Cells(i, 2).Value) Then
            nonmatch = nonmatch + 1
           
        End If
         i = i + 1
    Wend
  
  If (Count = nonmatch) Then
        j(n) = Worksheets("Sheet1").Cells(n, 10)
      MsgBox j(n)
   End If

Next



End Sub