Function fR() – Return row number where Content is found in Column with specified Header

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
 
    Else
 
        Debug.Print "Couldn't find " & Name
 
    End If
 
End Sub

Video sample

https://en.excelguru.no/wp-content/uploads/2021/09/CommonLibrary-fRSample00.mp4

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
    Else
        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)
    Else
       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
        Else
            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
       
    Else

        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)