VBA: Intersect/Match Row and Column to Select Cell

JennV

New Member
Joined
May 9, 2019
Messages
34
EggsSausageToastBacon
Jun 1
Jun 2
Jun 3x
Jun 4

<tbody>
</tbody>

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!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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"

Code:
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
 
Last edited:
Upvote 0
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"

Code:
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?
 
Upvote 0
Try this:
Note: Dim RowText As Date (not as string)

Code:
[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]
 
Upvote 0
Try this:
Note: Dim RowText As Date (not as string)

Code:
[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!!!
 
Upvote 0
You're welcome, glad to help, & thanks for the feedback.:)
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top