Find, Match, Compare or Search.... Help!

[Guest]

Using XL 2000

I'm trying to build a click event that will find matches in each of the 8
worksheet cells and (where matched/found) list the value in column ZZ of the
current row in listbox1.

code so far...

Private Sub FindTest_Click()
ListBox1.Clear
Dim Cell As Range
For Each Cell In Worksheets("Data1").Range("F2:F" & LCount)
Set c = Cell(1, 8).Find(SecondOp, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
ListBox1.AddItem Cell(1, 1)
ListBox1.List(ListBox1.ListCount - 1, 1) = Format(Cell(1, 5),
"dd-mmm-yyyy")
Set c = Cell(1, 8).FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
Next
End Sub

The above (hacked from the help page) only checks the contents of a single
cell.

I need to adapt this routine to take an additional 8 selections and find the
same phrase/number selected in the corrosponding worksheet cells. The
worksheet cells contain a mixture of numbers and phrases, each sepreated by a
"|"
example; A1 = Brass|Steel|Aluminium

Two of these additions are required to find a numeriacal value that is > and
< than the values in B1 and C1 respectively.

When a the search criterion is matched in every cell (in the target row) the
value in Cell ZZ of the same target row is added to listindex1

The worksheet contains up to 1,000 rows.

A tall order I know, any ideas appreciated.

Many thanks,

Paul

 

[Tom Ogilvy]

If your code works on one cell, then expand that reference to include all
the 8 cells you want to check.

for example, to check 3 cells

set rng = Range("H1,M12,Z32")
Set c = rng.Find(SecondOp, LookIn:=xlValues)

and then

Set c = rng.FindNext(c)


Other than that, my hat is off to anyone that can understand the rest of
your explantion.

 

[Guest]

two values missing...try this:

Private Sub FindTest_Click()
ListBox1.Clear
Dim Cell As Range
dim source as range
dim text as string
dim lcount as long
dim SecondOp as string
lcount = 9 ''???
SecondOp= "???"
Set source = Worksheets("Data1").Range("F2:F" & LCount)
set cell = source.Find(SecondOp, LookIn:=xlValues)
If Not cell Is Nothing Then
firstAddress = cell.Address
Do
text = format(cell.value,"dd-mmm-yyyy")
ListBox1.AddItem text
Set cell = source.FindNext(cell)
Loop While cell.Address <> firstAddress
End If
Next
End Sub

Patrick Molloy
Microsoft Excel MVP