Find active cell location based on column heading and cell value

[Ariseamel]

So, I have mapped the different areas of our lab space on different tabs (in the attached file I have narrowed it down to 1 lab space for the sake of file size). What I would like to do is to create a command button for equipment search (shown on the SEARCH tab). Ideally I would like to select the lab and location from separate drop down menus (E4 and F4 respectively). Once those are selected, the user would then click on the "Then Click Here!" Command button. Clicking the command button should activate a sub to find the sheet (listed in E4) and the location (listed in F4) in the table named Map and activate that sheet and cell. For example, if I select QC lab location B, the sub should look at the Map table, find the value of the cell in the that corresponds to Column.Row = QC lab.B. The sub should then activate the QC lab sheet and activate the cell it found at QC lab.B (cell in this case is AM32).

TIA!

[JohnTopley]

Please Login or Register  to view this content.

"Location_tbl","Labs","Locations" are named ranges

[Ariseamel]

Works great on the example, but the below line causes a Run-time error "1004" Method "Range" of object "_Worksheet" failed.

cell = Application.Index(Range("Location_Tbl"), Application.Match(location, Range("Locations"), 0), Application.Match(Lab, Range("Labs"), 0))

I don't see where Location_Tbl is named as a range. Could that be my problem?

[JohnTopley]

Look under Formulas>>Name Manager where you will see the named ranges defined: you need to do the same in your actual w/book.

[Ariseamel]

OK, so I created the named ranges Locations, Labs, and Location_Tbl, but now I am getting a type mismatch error for the same line.

[Ariseamel]

Looking at it further, the code given has the same type mismatch error when a location is selected that is not valid for the lab. I tried QC Lab at Location 23 (which doesn't exist) and got the type mismatch error. I think it may be related to the blanks in the Location_Tbl?

[JohnTopley]

Please Login or Register  to view this content.

[JohnTopley]

Please Login or Register  to view this content.

[Ariseamel]

Bless you! It works wonderfully! Thank you so much!