Searching Multiple Columns 1

[mveera]

Hi,

I need to search an excel worksheet and get the rows which match the condition say col1="One", col2 ="Two" etc.

As of now what i do is loop thru' the used range of rows and for each row i check if col1="One" etc.

Is there any built in function like .Find where i can search for multiple columns instead of looping thru' each row?

Thanks
Veera

 

[Stretchwickster]

I found this in the help file:

Find Method Example

This example finds all cells in the range A1:A500 on worksheet one that contain the value 2, and then it makes those cells gray.

With Worksheets(1).Range("a1:a500")
    Set c = .Find(2, lookin:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            c.Interior.Pattern = xlPatternGray50
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With

Clive

 

[Stretchwickster]

Sorry I hit submit before I explained the function!

Finds specific information in a range, and returns a Range object that represents the first cell where that information is found. Returns Nothing if no match is found. Doesn’t affect the selection or the active cell.

expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)

The only required parameter is "What".
Here's a more applicable example to your situation:

Range("A1:C50").Find "One"

Clive

 

[mveera]

Thanks for the Reply.

But this searches only for a single value in all rows.
but what i want is search in col1 ="One", Col2="Two" in all rows. What i want is more like writing Query to a DB

Select rows excelworksheet where col1="One" and col2="Two"

Thanks
Veera

 

[RobBroekhuis]

I think the most straightforward way of doing it is to use the .find method for the most restrictive search (the one likely to yield the fewest results), and then programmatically check the other columns for each hit. If the order of the rows were not important, you could do a data-sort of your sheet, using criteria for each of the columns, to lump all of the relevant rows together.
Rob

 

[Zathras]

If you are doing this yourself interactively, and if your data are set up in a database format (one row of column names on top of the data rows) consider using Data/Filter/AutoFilter.

1. Select A1, click Data/Filter/AutoFilter
2. Select "One" from the drop-down on Col1
3. Select "Two" from the drop-down on Col2

The selections are cumulative and what you have remaining in view are only those rows that have "One" in Col1 and "Two" in Col2.

To remove the auto-filter, click the menu again. It is a toggle.

--- OR ---

As an alternative, depending on what you actually need to do with the resulting selection, database functions may also be a good way to go.

 

[mveera]

The user will have a drop down list of possible criteria in the form.

when they chose a particular criteria i will display the details in the fields. I am not sure how i can use database operations to get the result. The order of rows is not a problem for me.

Using a single column to filter first and then coding to find the others is a better option but what i am looking write now is 1000-2000 rows. this might increase. thats is reason i am little worried about the performance.

Thanks
Veera

 

[RobBroekhuis]

Does each set of criteria depend on a different set of columns, or are the columns being searched always the same? In the latter case, and if you really don't care about the order of the rows, why not keep the list permanently sorted? That way you can go directly (more or less) to the entries you need.
If that's not a go, I would look into the advanced filter capabilities - they would be able to do exactly what you need, after a little additional programming.
Rob

 

[mveera]

The searched columns are always the same.

Only thing is that some columns could be empty and those should not be considered for searching.

i think it is getting more complicated.
i tried using Advance Filter and i am getting the result.
I think i am not using it right.

In the "List Range" i select the entire range i will ti select says rows 1-250.

in the Criteria range i select a particular row say row10(col1-col10).

I select the copy to range, but it prints all the data again.


Thanks
Veera

 

[RobBroekhuis]

Take a careful look at Excel's help on advanced filter, especially the topic named "Examples of advanced filter criteria" is useful. The criteria range is an altogether separate range where you put multiple criteria that define your search/selection. Play with it a little bit (easiest to do this first in Excel proper, not through VBA) - if you think it might work for you and you need additional help, let us know.
Rob

 

[mveera]

Thank You Very Much.

I used Advanced Filter and it works fine. Now i will have to try using VBA to do the same.

Thanks
Veera

 

[mveera]

I think we cannot use the Advanced Filter functionality in VBA. There is no method like "find" to execute the "Advanced Filter" with options without opening the dialog box.

Veera

 

[RobBroekhuis]

Take a look through the following method (from VBA's help). I think it will work for you.

AdvancedFilter Method

Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

Syntax

expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

expression Required. An expression that returns a Range object.

Action Required Long. The filter operation. Can be one of the following XlFilterAction constants: xlFilterInPlace or xlFilterCopy.

CriteriaRange Optional Variant. The criteria range. If this argument is omitted, there are no criteria.

CopyToRange Optional Variant. The destination range for the copied rows if Action is xlFilterCopy. Otherwise, this argument is ignored.

Unique Optional Variant. True to filter unique records only. False to filter all records that meet the criteria. The default value is False.

Rob

 

[mveera]

Thank You.

I was looking at Excel Help amd thought that it is not available for VBA.

This method will be of great help to me.

Veera

 

[RobBroekhuis]

Just about anything available in Excel is available, in some form, in Excel VBA. Not always in the most easily findable form, but the macro recorder can usually clarify things.
Good luck implementing this in your application. Rob