Resources
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
Connect on Facebook

aHeaderH / CheckHeader

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
Powered by Create your own unique website with customizable templates.
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma