Find active cell location based on column heading and cell value

**Ariseamel** · 02-16-2024, 01:20 PM

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** · 02-16-2024, 02:33 PM

Private Sub CommandButton2_Click()
Dim Lab As String, location As String, cell As String
    With Sheets("Search")
        Lab = [E4]: location = [F4]
        cell = Application.Index(Range("Location_Tbl"), Application.Match(location, Range("Locations"), 0), Application.Match(Lab, Range("Labs"), 0))
    End With
    If cell = "" Then
        MsgBox "Location not valid for lab " & Lab, vbExclamation
        Exit Sub
    End If
    With Sheets(Lab)
        .Activate
        .Range(cell).Select
    End With
End Sub

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

**Ariseamel** · 02-16-2024, 02:56 PM

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** · 02-16-2024, 03:00 PM

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** · 02-16-2024, 03:21 PM

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** · 02-16-2024, 04:30 PM

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** · 02-16-2024, 06:18 PM

Private Sub CommandButton2_Click()
Dim Lab As String, location, cell As String, res
    With Sheets("Search")
        Lab = [E4]: location = [F4]
        res = Application.Index(Range("Location_Tbl"), Application.Match(location, Range("Locations"), 0), Application.Match(Lab, Range("Labs"), 0))
        If IsError(res) Then
            MsgBox "Location " & location & " not valid for lab " & Lab, vbExclamation
            Exit Sub
        Else
           cell = res
           If cell = "" Then
                MsgBox "Location " & location & " not valid for lab " & Lab, vbExclamation
                Exit Sub
           End If
        End If
    End With
        
    With Sheets(Lab)
        .Activate
        .Range(cell).Select
    End With
End Sub

**JohnTopley** · 02-17-2024, 03:48 AM

Private Sub CommandButton2_Click()
Dim Lab As String, location, cell As String, res
    With Sheets("Search")
        Lab = [E4]: location = [F4]
        res = Application.Index(Range("Location_Tbl"), Application.Match(location, Range("Locations"), 0), Application.Match(Lab, Range("Labs"), 0))
        cell = res
        If cell = "" Then
           MsgBox "Location " & location & " not valid for lab " & Lab, vbExclamation
           Exit Sub
        End If
    End With
        
    With Sheets(Lab)
        .Activate
        .Range(cell).Select
    End With
End Sub

**Ariseamel** · 02-20-2024, 09:53 AM

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

Find active cell location based on column heading and cell value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Re: Find active cell location based on column heading and cell value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Creating a macro that will format a group of cells based on the location of active cell

Looking up a column heading based data in a cell

Using offset function to select a range of cells based on active cell location

[SOLVED] VBA to find the nearest empty cell in column F and use that as the active cell.

Delete a column based on a heading cell value and loop

function to pull cell information from column based on cell location

Select a Range Based on Active Cell Location?

Bookmarks

Bookmarks

Posting Permissions