Dear Experts,

I cannot find any references to the following problem in any of my books,
although it seems to me it would be a common programming requirement!

I want to go to the cell in a range which is is the intersection of two
values, one in the rows, the other in the columns.

My example is a list of names in the rows, and dates in the columns. I want
to retrieve the value stored where NameX and DateY meet, but cannot find the
syntax for referencing that cell.

Any advice gratefully received.
Many thanks,

Matilda

Give this a try...

Sub FindIntersection()
Dim rngColumnToSearch As Range
Dim rngRowToSearch As Range
Dim rngColumnFound As Range
Dim rngRowFound As Range
Dim rngIntersection As Range
Dim wks As Worksheet

'Change the sheet row and column as necessary
Set wks = Sheets("Sheet1")
Set rngColumnToSearch = wks.Columns("A")
Set rngRowToSearch = wks.Rows(1)
'Change the next 2 lines What:="???"
Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole)
Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole)
If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then
Set rngIntersection = Intersect(rngColumnFound.EntireRow, _
rngRowFound.EntireColumn)
MsgBox rngIntersection.Address
End If

End Sub

--
HTH...

Jim Thomlinson


"Matilda" wrote:

Dear Experts,

I cannot find any references to the following problem in any of my books,
although it seems to me it would be a common programming requirement!

I want to go to the cell in a range which is is the intersection of two
values, one in the rows, the other in the columns.

My example is a list of names in the rows, and dates in the columns. I want
to retrieve the value stored where NameX and DateY meet, but cannot find the
syntax for referencing that cell.

Any advice gratefully received.
Many thanks,

Matilda

Thanks Jim, your code works exactly as I require.
I had written a do...while loop to find the name, and another for the dates,
but didn't know how to extract the Row value and the Column value from the
address to create the address of the intersection. I thought there would be
a means of referencing these values as variables in vba, but oviously not.
Your code is also a much neater way of arriving at the required addresses.
I'm learning the excel inbuilt functions slowly, and this sort of example
helps heaps.

Much obliged,

Matilda


"Jim Thomlinson" wrote:

Give this a try...

Sub FindIntersection()
Dim rngColumnToSearch As Range
Dim rngRowToSearch As Range
Dim rngColumnFound As Range
Dim rngRowFound As Range
Dim rngIntersection As Range
Dim wks As Worksheet

'Change the sheet row and column as necessary
Set wks = Sheets("Sheet1")
Set rngColumnToSearch = wks.Columns("A")
Set rngRowToSearch = wks.Rows(1)
'Change the next 2 lines What:="???"
Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole)
Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole)
If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then
Set rngIntersection = Intersect(rngColumnFound.EntireRow, _
rngRowFound.EntireColumn)
MsgBox rngIntersection.Address
End If

End Sub

--
HTH...

Jim Thomlinson


"Matilda" wrote:

Dear Experts,

I cannot find any references to the following problem in any of my books,
although it seems to me it would be a common programming requirement!

I want to go to the cell in a range which is is the intersection of two
values, one in the rows, the other in the columns.

My example is a list of names in the rows, and dates in the columns. I want
to retrieve the value stored where NameX and DateY meet, but cannot find the
syntax for referencing that cell.

Any advice gratefully received.
Many thanks,

Matilda

I found this code & is very close to what I need! Since I'm a novice at code
writing, I find what I need & then try to modify. I think this will work but
I need a "do loop" to select "This" and "That" (see original code) from a two
column table. (The code seems to work if I put in location of Ang & Rol in
place of "This" & "That"):

Set rngColumnFound = rngColumnToSearch.Find(What:=[F5], LookAt:=xlWhole)
Set rngRowFound = rngRowToSearch.Find(What:=[G5], LookAt:=xlWhole)

but I need to loop through the table. Can anyone help?

example table (of course table can have "n" rows):

Ang (Col F) Rol (Col G)
Row 5 2 2
2 4
4 6
n n

Thanks in advance,
dkk



"Matilda" wrote:


Thanks Jim, your code works exactly as I require.
I had written a do...while loop to find the name, and another for the dates,
but didn't know how to extract the Row value and the Column value from the
address to create the address of the intersection. I thought there would be
a means of referencing these values as variables in vba, but oviously not.
Your code is also a much neater way of arriving at the required addresses.
I'm learning the excel inbuilt functions slowly, and this sort of example
helps heaps.

Much obliged,

Matilda


"Jim Thomlinson" wrote:

Give this a try...

Sub FindIntersection()
Dim rngColumnToSearch As Range
Dim rngRowToSearch As Range
Dim rngColumnFound As Range
Dim rngRowFound As Range
Dim rngIntersection As Range
Dim wks As Worksheet

'Change the sheet row and column as necessary
Set wks = Sheets("Sheet1")
Set rngColumnToSearch = wks.Columns("A")
Set rngRowToSearch = wks.Rows(1)
'Change the next 2 lines What:="???"
Set rngColumnFound = rngColumnToSearch.Find(What:="This", LookAt:=xlWhole)
Set rngRowFound = rngRowToSearch.Find(What:="That", LookAt:=xlWhole)
If Not (rngColumnFound Is Nothing Or rngRowFound Is Nothing) Then
Set rngIntersection = Intersect(rngColumnFound.EntireRow, _
rngRowFound.EntireColumn)
MsgBox rngIntersection.Address
End If

End Sub

--
HTH...

Jim Thomlinson


"Matilda" wrote:

Dear Experts,

I cannot find any references to the following problem in any of my books,
although it seems to me it would be a common programming requirement!

I want to go to the cell in a range which is is the intersection of two
values, one in the rows, the other in the columns.

My example is a list of names in the rows, and dates in the columns. I want
to retrieve the value stored where NameX and DateY meet, but cannot find the
syntax for referencing that cell.

Any advice gratefully received.
Many thanks,

Matilda