Visual Basic Excel Find

[catbag]

Hi,

I would like to know if it possible from
Visual Basic to search for 3 different
values in excel and return their row number?
For example, I would like to know the row
number where a specific first name, last name, and age match. Looping through all the records takes too long. I tried a sheet.range().find(), but that only allows for one search criteria. Thanks


Catherine

[Tom Schreiner]

Hi
List some details as to the ranges involved.
Where are the First Name?
Last Names?
Ages?

Someone will be able to help you more specifically.

Tom

[Dave Hawley]

Hi Catherine


Not too sure I have understood, but try:

Sub FindIt()
Dim rFound As Range
Dim i As Integer

rFound = Range("A1")
    For i = 1 To WorksheetFunction.CountIf(Columns(1), "John")
    
        Set rFound = Columns(1).Find(What:="aa", After:=rFound, LookIn:=xlValues, LookAt _
                :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                False)
                
            If rFound.Offset(0, 1) = "Paul" And _
               rFound.Offset(0, 2) = 35 Then
             MsgBox "Found It"
             Exit For
            End If
     
    Next rFound
    
End Sub

[catbag]

Hi,

Sorry I wasn't clear in my post. I have
an excel spreadsheet that has about 4000
rows in it. I want to search for a name
in column B, a number in column C, and a
date in column E. So if I am looking for
Catherine, 21, 10-Apr-02, I want to know
the row number where I can find all these
values in the same row.

A B C D E
1 17 Catherine 15 a 10-Apr-02
2 29 Catherine 23 a 10-Apr-02
3 30 Tom 8 a 09-Apr-02
4 4 Dan 8 a 09-Apr-02
5 4 Catherine 21 a 10-Apr-02 <---
6 10 Tom 21 a 10-Apr-02

Thanks for you help,

Catherine

[ This Message was edited by: catbag on 2002-04-10 06:37 ]

[ This Message was edited by: catbag on 2002-04-10 06:38 ]