VBA code to locate intersection of a row and a column

kelly mort

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

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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 (.)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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