Using .Find to locate a row that meets multiple criteria

ProDill

New Member
Joined
Jul 27, 2015
Messages
2
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:

FruitColorLocation
AppleRedNew York
OrangeOrangeFlorida
PumpkinOrangeNew York
BananaYellowColumbia

<tbody>
</tbody>


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)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,499
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

New Member
Joined
Jul 27, 2015
Messages
2
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,499
Try:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,043
Messages
5,412,020
Members
403,409
Latest member
IHRAcer

This Week's Hot Topics

Top