Using .Find to locate a row that meets multiple criteria

[ProDill]

Hi I've always referred to these forums but I was unable to find the direct answer to my question this time so I figured it was time to create an account.

The basic idea of what I want to do is to use tag based logic to find the category that meets my specified criteria. At First I was just using INDEX/MATCH in the spread sheet, then I was using a WHILE loop with several IF loops to output what I needed but I need to do this hundred of times and neither of these methods are scalable for the work I want to do.

This is basically a layman's example for what I'm doing:

[TABLE="width: 211"]
<tbody>[TR]
[TD]Fruit[/TD]
[TD]Color[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Red[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Orange[/TD]
[TD]Florida[/TD]
[/TR]
[TR]
[TD]Pumpkin[/TD]
[TD]Orange[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Yellow[/TD]
[TD]Columbia[/TD]
[/TR]
</tbody>[/TABLE]


I want to output the fruit that is Orange and from New York. So my output should be "Pumpkin".
I know the basic syntax of .Find where I can say

Sub FindTest()


Dim c As Range


With Range("a2:c5")
Set c = .Find("New York")
End With



Range("F2").Value = c.Value
Range("F3").Value = c.Offset(0, -2).Value

End Sub

This outputs "New York" in F2 and "Apple" in F3.

Is there a way I can use this method to essentially say .Find("New York" && "Orange") in order to skip down to the row with all of the specified criteria in scalable way.

(I understand that this is a problem that would be way simpler in a database like SQL imported into Excel but basically I'm trying to figure out if I can do this at a larger scale using what I have on my home computer)

 

[JackDanIce]

Welcome to the board.

Have you tried recording a macro to filter for the values of interest? You can record a macro with two columns filtered, this should leave a visible set of cells that are of interest - post this code if this helps start you and then specify what changes are required (e.g. I'd like to output all unique values visible in column A after the filter to column F)

 

[ProDill]

I like where you're going with this, another way to think about what I'm doing is an iterations of filters. Basic example of what I started by doing it with your method is below. My next question becomes, of these different methods, what is the efficiency difference between them, Because when I end up doing these a few hundred times the cumbersome methods (i.e. Index/Match matrices) take a few minutes to load where I'm hoping a .Find or a Recorded Filter Macro runs more smoothly.

Anyways, this is what I have so far:


Sub Isolated()
'
' Isolated Macro
'
' Keyboard Shortcut: Option+Cmd+e
'
ActiveSheet.Range("$A$1:$BH$345").AutoFilter Field:=17, Criteria1:= _
"Isolated"
EndSub
Sub Back()
'
' Back Macro
'
' Keyboard Shortcut: Option+Cmd+g
'
ActiveSheet.Range("$A$1:$BH$345").AutoFilter Field:=2, Criteria1:= _
"Back"
EndSub


Sub BackIsolated()


Call Back
Call Isolated
ActiveSheet.Range("$A$1:$BH$345").AutoFilter Field:=5, Criteria1:= _
"3"



EndSub

So all this did was filter Columns 17, 2, and then I added column 5 to the filter. This returned Rows 54,55,73,101,109, and 110. The other rows are filtered out. Can I offset 1 down from A1 to get to A54? What is the best way to call our the value of that cell. Also Column 5 is numeric values from 1 to 6 which are inputs from a user form, how can I specify that I want values Less than 3. (I'm skipping a step because I would actually want my code to say "Less than TextBox1.Value" Where TextBox1 has an integer value.)

Thanks for the help!

 

[JackDanIce]

Try:

Sub Macro1()

    Dim a   As Long
    
    Application.ScreenUpdating = True
    
    With ActiveSheet
        
        If .AutoFilterMode Then .AutoFilterMode = False
        a = .Range("A" & .rows.Count).End(xlUp).Row
                
        With .Range("A1:BH1").Resize(x)
            .AutoFilter
            .AutoFilter field:=17, Criteria1:="Isolated"
            .AutoFilter field:=2, Criteria1:="Back"
            .AutoFilter field:=5, Criteria1:="<" & 3
            .Offset(1).Resize(x - 1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
        End With
        
    End With
    
    Application.ScreenUpdating = False
    
End Sub