VBA code to locate intersection of a row and a column

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,077
Office Version
2010
Platform
Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
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.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,077
Office Version
2010
Platform
Windows
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:

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,655
Office Version
2010
Platform
Windows
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?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
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 (.)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,655
Office Version
2010
Platform
Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,728
Office Version
2016
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,089,761
Messages
5,410,273
Members
403,305
Latest member
tray2014

This Week's Hot Topics

Top