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
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
'--------------------------------------------------------------------------------------------------------------------
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