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!
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
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:

JennV

New Member
Joined
May 9, 2019
Messages
34
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?
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
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]
 

JennV

New Member
Joined
May 9, 2019
Messages
34
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!!!
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,571
Office Version
365
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,842
Messages
5,489,204
Members
407,678
Latest member
Matt1989

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top