Usage example

Sub TestFR()
    Dim RowNo As Long
    Dim Name As String
    Name = "Zita"
    RowNo = fR(shData, "Name", Name, True)
    If RowNo <> 0 Then
        Dim EgId As Long
        EgId = shData.Cells(RowNo, 1)
        Debug.Print "Row number " & RowNo
        Debug.Print "Id " & EgId
        Debug.Print "Couldn't find " & Name
    End If
End Sub

Video sample

Function code

'Return row number from column
'with header Header that equals Content
Function fR(sh As Worksheet, Header As Variant, Content As Variant, Optional GoSilent As Boolean, Optional rowHeader As Long, Optional RowSearchStart As Long) As Long

    Dim TableTop As Long
    If rowHeader = 0 Then
        TableTop = 1
        TableTop = rowHeader
    End If

    Dim rngColumnHeader As Range
    Dim Col As Long
    If Left(Header, 1) <> "#" Then
        Set rngColumnHeader = sh.Range(TableTop & ":" & TableTop).Find(What:=Header, LookIn:=xlValues, Lookat:=xlWhole)
       Col = Val(Mid(Header, 2))
       Set rngColumnHeader = sh.Range(sh.Cells(TableTop, Col), sh.Cells(TableTop, Col))
    End If

    If Not rngColumnHeader Is Nothing Then

        Dim SearchStart As Long
        If RowSearchStart = 0 Then
            SearchStart = TableTop
            SearchStart = RowSearchStart
        End If

        With sh
            Dim rngEnd As Range
            Set rngEnd = .Range(.Cells(sh.UsedRange.Rows.Count + TableTop, rngColumnHeader.Column), .Cells(sh.UsedRange.Rows.Count + TableTop, rngColumnHeader.Column))
            Dim rngSearch As Range
            Set rngSearch = .Range(.Cells(SearchStart, rngColumnHeader.Column), .Cells(sh.UsedRange.Rows.Count + TableTop, rngColumnHeader.Column))
        End With

        Dim rngResult As Range
        Set rngResult = rngSearch.Find(What:=Content, LookIn:=xlValues, Lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, After:=rngEnd)
        If Not rngResult Is Nothing Then
            fR = rngResult.Row
        End If

        If GoSilent = False Then

            ErrMsg "Cannot find header '" & Header & "' in row " & TableTop & " in sheet " & sh.Name

        End If

    End If

End Function

The function fR is part of My Common Library: My Common Library (1 download)

Similar Posts