Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersec col and row (2000)

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

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •