VBA code to locate intersection of a row and a column

[kelly mort]

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

 

[offthelip]

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]

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]

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

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

 

[kelly mort]

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

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

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]

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]

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]

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...

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]

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...

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