VBA code to locate intersection of a row and a column
Results 1 to 9 of 9

Thread: VBA code to locate intersection of a row and a column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default VBA code to locate intersection of a row and a column

    I have these two variables:
    1. MyRow
    2. MyColumn

    I am identifying them with an input box entry

    So if myrow becomes A2 and mycolumn becomes F1 , then I am looking for a code to get that intersection (F2) for me.

    I will be placing some data at the intersection. But I don't know how to get the intersection.

    Can someone pull it out for me?

    Thanks
    There Is Always A Better Way

  2. #2
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    817
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    What type of data do you have in Myrow? is this just a number or is it a string including the letter
    What type of data do you have in Mycolumnn? is this just one or more letters or is it a string including the number
    The answer to these questions makes difference as to how the vba would deal with the variables
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  3. #3
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    Quote Originally Posted by offthelip View Post
    What type of data do you have in Myrow? is this just a number or is it a string including the letter
    What type of data do you have in Mycolumnn? is this just one or more letters or is it a string including the number
    The answer to these questions makes difference as to how the vba would deal with the variables
    They are strings but I am using a for each loop to get those variables.

    So I think its pointing to a cell reference meanwhile my knowledge is limited here.

    Let me know if there is more info you need.

    My big problem is that I can not post screen shot here because I am on phone.
    There Is Always A Better Way

  4. #4
    Board Regular
    Join Date
    Dec 2017
    Location
    UK
    Posts
    817
    Post Thanks / Like
    Mentioned
    35 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    I am not quite sure what you have got, but maybe this will help you:

    Code:
    Sub test2()
    Dim rng As Range, mycell As Range
    
    
    Set rng = Range("A1:A3")
    
    
    For Each cell In rng
    Myrow = cell.Row
    Mycolumn = cell.Column
    
    
    Cells(Myrow, Mycolumn) = "here" & Myrow & "/" & Mycolumn
    
    
    'or
    Cells(Myrow, Mycolumn).Select
    
    
    Next cell
    End Sub
    Last edited by offthelip; Jul 18th, 2019 at 01:13 PM.
    Speed up your code use variant arrays and NEVER ACCESS THE WORKSHEET IN A LOOP

  5. #5
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    Quote Originally Posted by offthelip View Post
    I am not quite sure what you have got, but maybe this will help you:

    Code:
    Sub test2()
    Dim rng As Range, mycell As Range
    
    
    Set rng = Range("A1:A3")
    
    
    For Each cell In rng
    Myrow = cell.Row
    Mycolumn = cell.Column
    
    
    Cells(Myrow, Mycolumn) = "here" & Myrow & "/" & Mycolumn
    
    
    'or
    Cells(Myrow, Mycolumn).Select
    
    
    Next cell
    End Sub
    Code:
    Sub CheckTest()
        Dim MyRow As Range, CellRow As Variant
        Dim ColItem As Variant, RowItem As Variant
        Dim MyCol As Range, CellCol As Variant
    
    
            RowItem = Application.InputBox("Enter the row item", "")
       
            For Each CellRow In Sheets("HOME").[A21:A50]
                If Replace(CellRow, " ", "") = Replace(UCase(Trim(RowItem)), " ", "") Then Set MyRow = RowItem.Row
            Exit For
            Next CellRow
        
            ColItem = Application.InputBox("Enter the column item", "")
        
            For Each CellCol In Sheets("HOME").[B20:O20]
                If Replace(CellCol, " ", "") = Replace(UCase(Trim(ColItem)), " ", "") Then Set MyCol = CellCol.Column
            Exit For
            Next CellCol
                
            Sheets("HOME").Cells(MyRow, MyCol) = "YES"
    End Sub
    These are the two loops I am using.

    Look at it and see where I am having the error.

    I looked at what you posted trying to fix my case but couldn't
    There Is Always A Better Way

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,964
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    You might be able to do away with those loops depending on your answer to these questions...

    Why are you replacing space with nothing before testing if the text from the InputBox is the same as in the cell?

    Would the text in the cell be guaranteed to only have single spaces between "words"?

    Also, can we conclude that the text in the cells is always uppercase?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  7. #7
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    Quote Originally Posted by Rick Rothstein View Post
    You might be able to do away with those loops depending on your answer to these questions...

    Why are you replacing space with nothing before testing if the text from the InputBox is the same as in the cell?

    Would the text in the cell be guaranteed to only have single spaces between "words"?

    Also, can we conclude that the text in the cells is always uppercase?
    1. I am replacing the space with nothing because when the space was there the code could not identify the match
    2. Yes there could be just one space or no space at all
    3. Yes there will always be upper case

    Meanwhile the text in cells could contain letters, or mixture of text, number and a period (.)
    There Is Always A Better Way

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,964
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    Quote Originally Posted by kelly mort View Post
    Code:
    Sub CheckTest()
        Dim MyRow As Range, CellRow As Variant
        Dim ColItem As Variant, RowItem As Variant
        Dim MyCol As Range, CellCol As Variant
    
    
            RowItem = Application.InputBox("Enter the row item", "")
       
            For Each CellRow In Sheets("HOME").[A21:A50]
                If Replace(CellRow, " ", "") = Replace(UCase(Trim(RowItem)), " ", "") Then Set MyRow = RowItem.Row
            Exit For
            Next CellRow
        
            ColItem = Application.InputBox("Enter the column item", "")
        
            For Each CellCol In Sheets("HOME").[B20:O20]
                If Replace(CellCol, " ", "") = Replace(UCase(Trim(ColItem)), " ", "") Then Set MyCol = CellCol.Column
            Exit For
            Next CellCol
                
            Sheets("HOME").Cells(MyRow, MyCol) = "YES"
    End Sub
    These are the two loops I am using.

    Look at it and see where I am having the error.
    To answer the question you are asking here, and if I understand what you are trying to do correctly, I believe (I did not test it) that the following modification of the code you posted should work...
    Code:
    Sub CheckTest()
        Dim MyRow As Long, CellRow As Range
        Dim ColItem As Variant, RowItem As Variant
        Dim MyCol As Long, CellCol As Range
    
        RowItem = Application.InputBox("Enter the row item", "")
    
        For Each CellRow In Sheets("HOME").[A21:A50]
            If Replace(CellRow, " ", "") = Replace(UCase(Trim(RowItem)), " ", "") Then
                MyRow = CellRow.Row
                Exit For
            End If
        Next CellRow
    
        ColItem = Application.InputBox("Enter the column item", "")
    
        For Each CellCol In Sheets("HOME").[B20:O20]
            If Replace(CellCol, " ", "") = Replace(UCase(Trim(ColItem)), " ", "") Then
                MyCol = CellCol.Column
                Exit For
            End If
        Next CellCol
            
        Sheets("HOME").Cells(MyRow, MyCol) = "YES"
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular kelly mort's Avatar
    Join Date
    Apr 2017
    Location
    Suhum, Ghana, West Africa
    Posts
    1,379
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA code to locate intersection of a row and a column

    Quote Originally Posted by Rick Rothstein View Post
    To answer the question you are asking here, and if I understand what you are trying to do correctly, I believe (I did not test it) that the following modification of the code you posted should work...
    Code:
    Sub CheckTest()
        Dim MyRow As Long, CellRow As Range
        Dim ColItem As Variant, RowItem As Variant
        Dim MyCol As Long, CellCol As Range
    
        RowItem = Application.InputBox("Enter the row item", "")
    
        For Each CellRow In Sheets("HOME").[A21:A50]
            If Replace(CellRow, " ", "") = Replace(UCase(Trim(RowItem)), " ", "") Then
                MyRow = CellRow.Row
                Exit For
            End If
        Next CellRow
    
        ColItem = Application.InputBox("Enter the column item", "")
    
        For Each CellCol In Sheets("HOME").[B20:O20]
            If Replace(CellCol, " ", "") = Replace(UCase(Trim(ColItem)), " ", "") Then
                MyCol = CellCol.Column
                Exit For
            End If
        Next CellCol
            
        Sheets("HOME").Cells(MyRow, MyCol) = "YES"
    End Sub
    Great!!!
    Problem solved.

    Very grateful
    There Is Always A Better Way

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
  •