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)
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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)
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top