VBA: Intersect/Match Row and Column to Select Cell
Results 1 to 6 of 6

Thread: VBA: Intersect/Match Row and Column to Select Cell

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Intersect/Match Row and Column to Select Cell

    Eggs Sausage Toast Bacon
    Jun 1
    Jun 2
    Jun 3 x
    Jun 4

    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!

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    177
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA: Intersect/Match Row and Column to Select Cell

    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 by shknbk2; Aug 22nd, 2019 at 08:48 PM. Reason: Change to selecting cell rather than putting an "x" in it.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Intersect/Match Row and Column to Select Cell

    Quote Originally Posted by shknbk2 View Post
    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
        
        Dim RowText As String
        RowText = Range("G5")
    
        Dim ColText As String
        ColText = Range("G6")
    
        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?

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,961
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA: Intersect/Match Row and Column to Select Cell

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

    Code:
    Sub a1107763a()
    'https://www.mrexcel.com/forum/excel-questions/1107763-vba-intersect-match-row-column-select-cell.html
        Dim RowText As Date
        Dim ColText As String
        Dim res
        
        If Not IsDate(Range("G5")) Then MsgBox "It's not date": Exit Sub
        
        RowText = CDate(Range("G5"))
        ColText = Range("G6")
    
    res1 = Application.Match(CLng(RowText), Range("A:A"), False)
    res2 = Application.Match(ColText, Rows("1:1"), False)
    
    If IsNumeric(res1) And IsNumeric(res2) Then
    Cells(res1, res2).Select
    Else
    MsgBox "Can't find such item"
    End If
    
    End Sub
    

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    32
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Intersect/Match Row and Column to Select Cell

    Quote Originally Posted by Akuini View Post
    Try this:
    Note: Dim RowText As Date (not as string)

    Code:
    Sub a1107763a()
    'https://www.mrexcel.com/forum/excel-questions/1107763-vba-intersect-match-row-column-select-cell.html
        Dim RowText As Date
        Dim ColText As String
        Dim res
        
        If Not IsDate(Range("G5")) Then MsgBox "It's not date": Exit Sub
        
        RowText = CDate(Range("G5"))
        ColText = Range("G6")
    
    res1 = Application.Match(CLng(RowText), Range("A:A"), False)
    res2 = Application.Match(ColText, Rows("1:1"), False)
    
    If IsNumeric(res1) And IsNumeric(res2) Then
    Cells(res1, res2).Select
    Else
    MsgBox "Can't find such item"
    End If
    
    End Sub
    
    Amazing! Thank you so, so much!!!

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,961
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA: Intersect/Match Row and Column to Select Cell

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •