Function findContent()

Find content in column1 with header xx, return value from same row in column2 with header yy.

Note

The function returns 0 (Zero) when value isn’t found, so you will not be able to distinguish between a found zero value, and value not found. So do not use if you have zero values in your table.
Use the GoSilent parameter = True if you know values might not be found.
GoSilent = False is useful for debugging.

Usage example

Sub TestfindContent()

    Dim SearchCol As String
    SearchCol = "Name"

    Dim SearchName As String
    SearchName = "Paula"

    Dim ReturnCol As String
    ReturnCol = "EgId"

    Dim EgId As Long
    EgId = findContent(shData, SearchCol, SearchName, ReturnCol, True)
    
    If EgId <> 0 Then
    
        Debug.Print EgId
    
    Else
    
        ErrMsg "Couldn't find " & SearchName & " in Column with header " & SearchCol
    
    End If

End Sub

Function Code

'**********************************************************
'1) Find ColumnHeader from Header in row 1
'2) Find ColumnReturnHeader from ReturnHeader in row 1
'3) Find row number where Content is found in ColumnHeader
'4) Returns value in ColumnReturnHeader from row in 3)
'5) Error message is supressed if GoSilent is true
'**********************************************************
Function findContent(sh As Worksheet, Header As Variant, Content As Variant, _
ReturnHeader As Variant, Optional GoSilent As Boolean, _
Optional rowHeader As Long) As Variant

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

    Dim rngColumnHeader As Range
    Set rngColumnHeader = sh.Range(TableTop & ":" & TableTop). _
    Find(What:=Header, LookIn:=xlValues, Lookat:=xlWhole)
    
    Dim rngColumnReturnHeader As Range
    Set rngColumnReturnHeader = sh.Range(TableTop & ":" & TableTop). _
    Find(What:=ReturnHeader, LookIn:=xlValues, Lookat:=xlWhole)
    
    If Not (rngColumnHeader Is Nothing Or rngColumnReturnHeader Is Nothing) Then

        
        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(1, 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
            
            findContent = sh.Cells(rngResult.Row, rngColumnReturnHeader.Column)
        
        End If

    Else
    
        If GoSilent = False Then
    
            Dim msg As String
    
            If rngColumnHeader Is Nothing Then
                msg = "Could not find Header '" & Header _
                & "' in row " & TableTop & " in sheet " & sh.Name
            End If
    
            If rngColumnReturnHeader Is Nothing Then
                msg = msg & Chr(10) & "Could not find ReturnHeader '" & ReturnHeader _
                & "' in row " & TableTop & " in sheet " & sh.Name
            End If
        
            If msg <> "" Then
                ErrMsg msg
            End If
    
        End If

    End If

End Function

The function findContent is part of My Common Library: My Common Library (1438 downloads )