# Thread: Intersec col and row (2000)

1. ## Intersec col and row (2000)

Admit have this var:

SPORT = "1743"
SERV = "ES"

and range to find SPORT N2:BH77
and range to find SERV F2:F77

how to intersect this 2 var and return the value of cell finded similar FIND=CELL.row?

In this case then value of FIND is 24
Attached sheet

2. ## Re: Intersec col and row (2000)

You could create a function:

<pre>Option Explicit
Function FindRow(sSport As String, sServ As String, rSport As Range, rServ As Range)
Dim bSport As Boolean
Dim lRow As Long
Dim iCol As Integer
Dim x As Long

x = 0 'Value for "not found"
bSport = False
For lRow = 1 To rServ.Rows.Count
If rServ.Cells(lRow, 1).Value = sServ Then
For iCol = 1 To rSport.Columns.Count
bSport = (rSport(lRow, iCol) = sSport)
If bSport Then Exit For
Next
If bSport Then
x = rServ(lRow, iCol).Row
Exit For
End If
End If
Next
FindRow = x
End Function[/quote]

It could be used as a formula in a cell:
[pre]=findrow("1743", "ES",N2:BH77,F2:F77)</pre>

Or called in a macro
<pre>Sub Testme()
Dim SPORT As String
Dim SERV As String
Dim l As Long
SPORT = "1743"
SERV = "ES"
l = FindRow(SPORT, SERV, Range("N2:BH77"), Range("F2:F77"))
MsgBox l
End Sub</pre>

If there is no intersection, the function will return zero (0).

Steve

3. ## Re: Intersec col and row (2000)

Hi Steve , sorry for delay...
The code work PERFECT!!!!!!!!!!!!!!
Tks.

4. ## Re: Intersec col and row (2000)

hI Steve, sorry me...
But have the similar prob with this sheet.
I want to intersec column G and column N with:

REGIONE=PUGLIA
SERV=ES

i want to knoew the number of line for intersection of this 2 variable, in this case the line is = 43

Hope i clear.
tks for all.

5. ## Re: Intersec col and row (2000)

=findrow("ES","PUGLIA",G2:G77,N2:N77)

You can adapt the Macro in the same way and I leave that to you...
Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•