getTableValue

Function getTableValue(...) provides a flexible way for retrieving data from a range of cells (or table) within a spreadsheet tab. The function takes four parameters, the first parameter is the name of the tab with the table, second parameter is address of the upper left cell of the table and finally third and fourth parameters are row and column headings. The function returns a value from the table at the intersection of specified row and column heading. See Fig. 1 and the Load Combination spreadsheet for details of how this function is used.

Fig. 1 Illustration of getTableValue function parameters and return value

Function getTableValue(strTableSheet As String, strTableBeg As String, _
                       targetRowHeading As String, targetcolumnHeading As String)

    Dim row, column, emptyCells As Integer
    Dim rowHeading, columnHeading As String
    Dim terminator As String
    Dim value As Variant
    row = 0
    column = 0
    terminator = "END" 'terminator used for row heading and column heading
      
    Do
        rowHeading = Sheets(strTableSheet).Range(strTableBeg).Offset(row, 0).value
        If (rowHeading = targetRowHeading) Then
           Exit Do
        End If
        row = row + 1
    Loop While rowHeading <> terminator

    Do
        columnHeading = Sheets(strTableSheet).Range(strTableBeg).Offset(0, column).value
        If (columnHeading = targetcolumnHeading) Then
             Exit Do
        End If
        column = column + 1
    Loop While rowHeading <> terminator

    value = Sheets(strTableSheet).Range(strTableBeg).Offset(row, column).value
    If (Not IsNumeric(value)) Then
        value = 0
    End If
    If ((rowHeading <> terminator) And (columnHeading <> terminator)) Then
        getTableValue = value
    Else
        getTableValue = "**ERROR**"
    End If

End Function

Main Menu