VBA: Find Partial match column from array

[vijkar2008]

Hi,
I am looking for a partial match syntax for array. I have declared array as the following - column names of a consolidated sheet:
arrCurrentColumnNames = Array("State", "Line of Business", "Tracking Numbers")

I am using the below loop to write the headers. How do I change this below code to make the loop look for partial match of these headers in the array and use it for the rest of my macro?

For ex: Array has 'State' and the column of data sheet has 'Two Digit State'. I still want the macro to get the values from 'Two Digit State' column and paste it under 'State' in the destination sheet.

'Write headers
For numColumnCounter = 0 To UBound(arrCurrentColumnNames)
'If InStr(1, arrCurrentColumnNames(numColumnCounter), vbTextCompare) > 0 Then

ThisWorkbook.Sheets("Consolidated").Cells(1, numColumnCounter + 1).Value = arrCurrentColumnNames(numColumnCounter)
'End If

Next

The above loop is just one where I want to do partial match of array value. Any help is much appreciated. Thanks in advance!

 

[Alex Blakenburg]

See if this helps.
It needs some tidying up since I didn't know where your headings are positioned or what sheet the headings are on.
I have assumed a range on row 1 and the the headings we are looking for are on the active sheet.

Sub FindHdgColumns()

    Dim rngHdr As Range
    Dim arrCurrentColumnNames As Variant
    Dim numColumnCounter As Long
    Dim colNoHdr As Long
    
    arrCurrentColumnNames = Array("State", "Line of Business", "Tracking Numbers")
    Set rngHdr = Range("A1:Z1")

    'Write headers
    For numColumnCounter = 0 To UBound(arrCurrentColumnNames)
        With Application
            colNoHdr = .IfError(.Match("*" & arrCurrentColumnNames(numColumnCounter) & "*", Range("A1:Z1"), 0), 0)
        End With
        
        ' ---------------  Testing only -----------------------------------
        Debug.Print "Heading Text: " & vbTab & arrCurrentColumnNames(numColumnCounter) & vbLf _
                    ; "Col No found: " & vbTab & colNoHdr
        ' -----------------------------------------------------------------
        If colNoHdr = 0 Then
            MsgBox arrCurrentColumnNames(numColumnCounter) & "   Not found"
        End If
         
        ' I had this next line commented out, it doesn't really seem to be using the "found" column no
        ThisWorkbook.Sheets("Consolidated").Cells(1, numColumnCounter + 1).Value = arrCurrentColumnNames(numColumnCounter)
    Next

End Sub