Macro help for record lookup/filtering in excel Greetings! Background I am tryin

Question

Macro help for record lookup/filtering in excel

Greetings!

Background

I am trying to build a simple search function in excel. The user will start from a sheet (Sheet 1) that will allow him/her to specify up to three search criteria (hotel name, hotel city, hotel rate type).

The hotel data is located on a second sheet (Sheet 2). The data set contains hotel name, hotel city, hotel rate type and a number of other attribute fields, but only those three fields are involved in the search.

On the search sheet, the user selects the search criteria from any one of three cells that use data validation lists pulled from the hotel data.

Request for Help

Once the criteria are selected, the user will click the 'Search' button. I need the macro to produce a hotel data set that is filtered by any one of those three criteria. I don't really know VBA so I rely heavily on the record macro feature to build macros. I think I know how to design it, but I need some help with the programming language. Of course if my design concept is way off, I'm welcome to input there as well.

Design

1) Create 3 string variables (one for each search criteria)

2) Using the autofilter function, populate the criteria with the String variable

Now, how do I code this?

Sub Macro1()
'
' Macro1 Macro
'

Dim Hotel Name as String

Dim Hotel City as String

Dim Hotel Rate Type as String

Hotel Name= [what do I put here? the Hotel Name search cell address?]

Hotel City = [what do I put here? the Hotel city search cell address?]

Hotel Rate Type = [what do I put here? the Hotel city search cell address?]

Sheets("Sheet 2").Select

ActiveSheet.Range("$A$1:$HB$485").AutoFilter Field:=1, Criteria1:= [this should be the string variable for the Hotel Name. what do I put here?]

ActiveSheet.Range("$A$1:$HB$485").AutoFilter Field:=2, Criteria1:= [this should be the string variable for the Hotel City. what do I put here?]

ActiveSheet.Range("$A$1:$HB$485").AutoFilter Field:=3, Criteria1:= [this should be the string variable for the Hotel Rate Type. what do I put here?]

End Sub

Thanks !

Explanation / Answer

Hi..

Here is an example.

Sub Macro1()

hotel_name = Sheets("Sheet1").Range("B1").Value
hotel_city = Sheets("Sheet1").Range("B2").Value
hote_rate = Sheets("Sheet1").Range("B3").Value

Sheets("Sheet2").Select
ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=1, Criteria1:=hotel_name
ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=2, Criteria1:=hotel_city
ActiveSheet.Range("$A$1:$C$20").AutoFilter Field:=3, Criteria1:=hote_rate

End Sub

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.