Public Array Table: [Header]

Dim aHeaderH() As Variant
 
Function LoadPublicHeaderH(myWsIndex As Long)
'Template: 1) Replace "aHeaderH" with object to use / 2)Replace sHeader value in Quotes
'-------------------------------------------------------------------------------------------------------------------
Dim sHeader As String, iUbound As Long, aHeaderSplit As Variant, iSubLoop As Long
Dim iCe1 As Long, iLoop As Long
 '-------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------
'Select the Dash desired and set the Object [Last Column]
iCe1 = Range(GetLastCell(myWsIndex)).Column
'-------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------
'This is the string of STANDARD Headers for MONTH
sHeader = "Cal 1^Cal 2^NFPA 1^NFPA 2^UFAC 1^UFAC 2^FAA 1^FAA 2^DMV 1^DMV 2^BIFMA 1^BIFMA 2^ASTM 1^ASTM 2^British 1^British 2^CPAI 1^CPAI 2^IMO 1^IMO 2^Can 1^Can 2^IBC 1^IBC 2^UBC 1^UBC 2^Boston 1^Boston 2^"
'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS
'Split String
aHeaderSplit = Split(sHeader, "^")
    iUbound = UBound(aHeaderSplit)
   '-------------------------------------------------------------------------------------------------------------------
    'Reset the Array, then define: Ubound(1), Ubound(2)
    Erase aHeaderH
    ReDim aHeaderH(1 To 2, 1 To iUbound)
    '-------------------------------------------------------------------------------------------------------------------
   
'Stage 1: Split the string - load into Array(Header Row=1)'Load the Column Header (Row 1)
For iLoop = LBound(aHeaderSplit) To UBound(aHeaderSplit)
    If iLoop + 1 <= iUbound Then aHeaderH(1, iLoop + 1) = aHeaderSplit(iLoop)
Next iLoop
'SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS
'-------------------------------------------------------------------------------------------------------------------
 
'-------------------------------------------------------------------------------------------------------------------
''Stage 2: Load the Column Location (Row 2)
For iSubLoop = LBound(aHeaderH, 2) To UBound(aHeaderH, 2)
       
    For iLoop = 1 To iCe1
        If UCase(Worksheets(myWsIndex).Cells(1, iLoop)) = UCase(aHeaderH(1, iSubLoop)) Then
            'Erased the array at the beginning of this code - only load the col for the FIRST match found (in case of multiple columns with the SAME header)
            If aHeaderH(2, iSubLoop) = "" Then aHeaderH(2, iSubLoop) = iLoop
        End If
   
    Next iLoop
Next iSubLoop
'-------------------------------------------------------------------------------------------------------------------
 
Erase aHeaderSplit
End Function

---

CheckHeader(String,Ws.Index, HeaderRow, EndCol)

Notes:

• Memorizes Active Sheet - No need to worry about re-selecting if calling during a procedure
  
• Returns 0 if no match
  
• Tests Ucase values - Does NOT use Instr() method
  
• If Test Value is found more than once in the Row being checked - it sets the result using the FIRST match.
  

Public Function CheckHeader(iMyText As String, iMyWs As Long, iMyRow As Long, iMyEndCol As Long) As Double
'--------------------------------------------------------------------------------------------------------------------
Dim iTempWorksheet As Long, iLoopCol As Long
iTempWorksheet = ActiveWorkbook.ActiveSheet.Index
'--------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------
Worksheets(iMyWs).Select
CheckHeader = 0
'--------------------------------------------------------------------------------------------------------------------
'--------------------------------------------------------------------------------------------------------------------
For iLoopCol = 1 To iMyEndCol
    If UCase(Cells(iMyRow, iLoopCol)) = UCase(iMyText) Then
        CheckHeader = iLoopCol
        Exit For
    Else
    End If
Next iLoopCol
'--------------------------------------------------------------------------------------------------------------------
Worksheets(iTempWorksheet).Select
End Function