Return Cell Address w/ Multiple Criteria Lookup

[ExcelRabbit]

Hello,

Hello,

atm I have a spreadsheet with the following data:-
Column A Column B Column C
1 01/05/03 APPLES 1KG
2 01/05/03 ORANGES 3KG
3 01/05/03 PEARS 1.5KG
4 02/05/03 ORANGES 2.5KG

I am trying to use find in vb to pick up the cell address of C after matching the 2 criteria. The criteria is date and product (ie 01/05/03 and PEARS).

Thanks

 

[Andrew Poulsom]

Re: help with vb

Something like this?

Sub Test()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim TheDate As Date
    Dim Fruit As String
    Dim Found As Boolean
    Dim c As Range
    Dim Addr As String
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    TheDate = DateSerial(2003, 5, 1)
    Fruit = "PEARS"
    Found = False
    Set c = Rng.Find(What:=TheDate)
    If Not c Is Nothing Then
        Addr = c.Address
        Do
            If c.Offset(0, 1).Value = Fruit Then
                Found = True
                MsgBox "Weight is in cell " & c.Offset(0, 2).Address(False, False)
                Exit Do
            Else
                Set c = Rng.FindNext(c)
            End If
        Loop While Not c Is Nothing And c.Address <> Addr
    Else
        MsgBox "Date not found"
    End If
    If Found = False Then
        MsgBox "Fruit not found for that date"
    End If
End Sub

 

[ExcelRabbit]

Re: help with vb

The above code works fine, except when I try to convert it into a function. Basically, I need 2 varaibles (the date, fruit).

Any suggestions?

 

[NateO]

Re: help with vb

Hello Rabbit, I changed your subject title something far less vague... Based on your posts, you might not even want VBA...

The above code works fine, except when I try to convert it into a function. Basically, I need 2 varaibles (the date, fruit).

Any suggestions?

This looks strikingly familiar! Perhaps I can get a few birds with one stone. Well, I took the liberty of testing my proc. at the linked thread, looks like I slipped an extra if in there, remove that and it's functional.

Here's the function, same deal pretty much:

Function Fin_Pears2(ByVal n As Date) As String
Dim OrigCl As String, Cl As Range
Set Cl = [a:a].Find(n)
If Not Cl Is Nothing Then
    If Cl(, 2) = "PEARS" Then
         Fin_Pears2 = Cl(, 3).Address(False, False)
         Set Cl = Nothing: Exit Function
    End If
    OrigCl = Cl.Address
    Do
        Set Cl = Cells.FindNext(Cl)
        If Not Cl Is Nothing And Cl(, 2) = "PEARS" Then
              Fin_Pears2 = Cl(, 3).Address(False, False)
              Set Cl = Nothing: Exit Function
        End If
    Loop Until Cl.Address = OrigCl
    Set Cl = Nothing
End If
Fin_Pears2 = "No Matches Found"
End Function


Sub test()
MsgBox Fin_Pears2("1/5/2003")
End Sub

But if you want to use it in a worksheet, you'll need to modify the function or use something like the following array function:

Book1
	A	B	C	D
1	1/5/2003	APPLES	1KG	C3
2	1/5/2003	ORANGES	3KG	C4
3	1/5/2003	PEARS	1.5KG	C1
4	2/5/2003	ORANGES	2.5KG	C2
Sheet3

Hope this helps.