VBA: Intersect/Match Row and Column to Select Cell

[JennV]

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[TD]Eggs[/TD]
[TD]Sausage[/TD]
[TD]Toast[/TD]
[TD]Bacon[/TD]
[/TR]
[TR]
[TD]Jun 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun 3[/TD]
[TD][/TD]
[TD="align: center"]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jun 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello,

I'm looking for a code to find matching column and row and select that cell. For example, for Sausage and Jun 3, select the 'x' cell.

I'm thinking of the cell.find coding but I think that's only row or only column and not the combination of both?

Any help is much appreciated, thank you!

 

[shknbk2]

You are right that the Cell.Find would not find both at the same time. Therefore, use it twice to find the column and then the row. Call this sub to select the intersection of RowText and ColText.

You can use it like this: SelectCell "Jun 2", "Toast"

Sub SelectCell(RowText As String, ColText As String)
    Dim FoundCell As Range
    Dim LastCell As Range
    Dim ColumnRange As Range
    Dim RowRange As Range
    
    Set ColumnRange = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
    Set LastCell = ColumnRange.Cells(ColumnRange.Cells.Count)
    Set FoundCell = ColumnRange.Find(what:=ColText, after:=LastCell)
    If Not FoundCell Is Nothing Then
        Set ColumnRange = FoundCell
        Set FoundCell = Nothing
        Set RowRange = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
        Set LastCell = RowRange.Cells(RowRange.Cells.Count)
        Set FoundCell = RowRange.Find(what:=RowText, after:=LastCell)
        If Not FoundCell Is Nothing Then
            Cells(FoundCell.Row, ColumnRange.Column).Select
        End If
    End If
End Sub

 

[JennV]

You are right that the Cell.Find would not find both at the same time. Therefore, use it twice to find the column and then the row. Call this sub to select the intersection of RowText and ColText.

You can use it like this: SelectCell "Jun 2", "Toast"

Sub SelectCell()
    Dim FoundCell As Range
    Dim LastCell As Range
    Dim ColumnRange As Range
    Dim RowRange As Range
    
[COLOR=#ff0000]    Dim RowText As String[/COLOR]
[COLOR=#ff0000]    RowText = Range("G5")[/COLOR]
[COLOR=#ff0000]    [/COLOR]
[COLOR=#ff0000]    Dim ColText As String[/COLOR]
[COLOR=#ff0000]    ColText = Range("G6")[/COLOR]

    Set ColumnRange = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
    Set LastCell = ColumnRange.Cells(ColumnRange.Cells.Count)
    Set FoundCell = ColumnRange.Find(what:=ColText, after:=LastCell)
    If Not FoundCell Is Nothing Then
        Set ColumnRange = FoundCell
        Set FoundCell = Nothing
        Set RowRange = Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp))
        Set LastCell = RowRange.Cells(RowRange.Cells.Count)
        Set FoundCell = RowRange.Find(what:=RowText, after:=LastCell)
        If Not FoundCell Is Nothing Then
            Cells(FoundCell.Row, ColumnRange.Column).Select
        End If
    End If
End Sub

Thank you for replying! I added the above in red to give users the flexibility to enter a date and type of food in cells G5 and G6. The code runs without errors but the desired cell is not selected. Any ideas as to why?

 

[Akuini]

Try this:
Note: Dim RowText As Date (not as string)

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107763a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107763-vba-intersect-match-row-column-select-cell.html[/COLOR][/I]
    [COLOR=Royalblue]Dim[/COLOR] RowText [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Date[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] ColText [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] res
    
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] IsDate(Range([COLOR=brown]"G5"[/COLOR])) [COLOR=Royalblue]Then[/COLOR] MsgBox [COLOR=brown]"It's not date"[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    
    RowText = [COLOR=Royalblue]CDate[/COLOR](Range([COLOR=brown]"G5"[/COLOR]))
    ColText = Range([COLOR=brown]"G6"[/COLOR])

res1 = Application.Match([COLOR=Royalblue]CLng[/COLOR](RowText), Range([COLOR=brown]"A:A"[/COLOR]), [COLOR=Royalblue]False[/COLOR])
res2 = Application.Match(ColText, Rows([COLOR=brown]"1:1"[/COLOR]), [COLOR=Royalblue]False[/COLOR])

[COLOR=Royalblue]If[/COLOR] IsNumeric(res1) [COLOR=Royalblue]And[/COLOR] IsNumeric(res2) [COLOR=Royalblue]Then[/COLOR]
Cells(res1, res2).[COLOR=Royalblue]Select[/COLOR]
[COLOR=Royalblue]Else[/COLOR]
MsgBox [COLOR=brown]"Can't find such item"[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[JennV]

Try this:
Note: Dim RowText As Date (not as string)

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1107763a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1107763-vba-intersect-match-row-column-select-cell.html[/COLOR][/I]
    [COLOR=Royalblue]Dim[/COLOR] RowText [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Date[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] ColText [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]
    [COLOR=Royalblue]Dim[/COLOR] res
    
    [COLOR=Royalblue]If[/COLOR] [COLOR=Royalblue]Not[/COLOR] IsDate(Range([COLOR=brown]"G5"[/COLOR])) [COLOR=Royalblue]Then[/COLOR] MsgBox [COLOR=brown]"It's not date"[/COLOR]: [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
    
    RowText = [COLOR=Royalblue]CDate[/COLOR](Range([COLOR=brown]"G5"[/COLOR]))
    ColText = Range([COLOR=brown]"G6"[/COLOR])

res1 = Application.Match([COLOR=Royalblue]CLng[/COLOR](RowText), Range([COLOR=brown]"A:A"[/COLOR]), [COLOR=Royalblue]False[/COLOR])
res2 = Application.Match(ColText, Rows([COLOR=brown]"1:1"[/COLOR]), [COLOR=Royalblue]False[/COLOR])

[COLOR=Royalblue]If[/COLOR] IsNumeric(res1) [COLOR=Royalblue]And[/COLOR] IsNumeric(res2) [COLOR=Royalblue]Then[/COLOR]
Cells(res1, res2).[COLOR=Royalblue]Select[/COLOR]
[COLOR=Royalblue]Else[/COLOR]
MsgBox [COLOR=brown]"Can't find such item"[/COLOR]
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

Amazing! Thank you so, so much!!!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.