Create a VBA Array from Column Headers?

Question

I have a an export "NewExport" that always randomizes the columns of data I receive. I need these columns to align with the order of columns in "TheOrder", so this code will help to re-organize the export to align with the column headers I've already built.

I have 132 columns that need re-alignment, and while I can type it all out, there must be an easier way to align with the column headers I've already created. It should be noted that the below code is shamelessly copy/pasted from another StackOverflow answer.

Sub OrderColumns(ByVal NewExport As Workbook, ByVal TheOrder As Worksheet)

Dim correctOrder() As Variant
Dim lastCol As Long
Dim headerRng As Range, cel As Range
Dim mainWS As Worksheet

Set mainWS = NewExport.Worksheets("Sheet1")

'Need to figure out how to make this an array based on a Range
correctOrder() = Array(TheOrder.Range("A1:A132").Value)

With mainWS
    lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    Set headerRng = .Range(.Cells(1, 1), .Cells(1, lastCol))
End With

Dim newWS As Worksheet
Set newWS = Ninja.Sheets.Add
newWS.Name = "Rearranged Sheet"

Dim col As Long
With newWS
    For col = 1 To lastCol
        For Each cel In headerRng
            If cel.Value = correctOrder(col - 1) Then
                mainWS.Columns(cel.Column).Copy .Columns(col)
                Exit For
            End If
        Next cel
    Next col
End With

End Sub

Answer 1

While it's not as automated as I would have liked (and requires one piece of hard-coding), I was able to find a solution as such:

Dim correctOrder(132) As Variant 
'132 will need to be changed if there's ever any more/less columns added/excluded

For i = 1 To 132
    correctOrder(i - 1) = TheOrder.Range("A" & i).Value
Next

This solution gave me the array I was looking for for use later on.

Answer 2

I recently wrote a 'column finder' function for a project of mine.

I've modified it to suit your requirements below.

• The function requires you pass the workbook your correct ordered headings are in to capture. You could modify this to require your TargetWorksheet instead so it's a bit more dynamic.
• The function returns a single dimension Array.
• The function finds the last used Column in the Target Worksheet allowing for changes in the number of column headings (as mentioned in your own answer which has the column number hard coded).

Public Function CorrectOrderHeadingsArrayFunction(ByRef TargetWorkbook As Workbook) As Variant()
    With TargetWorkbook.Sheets(1) 'Change this to target your correct sheet
        Dim LastColumn As Long
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        CorrectOrderHeadingsArrayFunction= Application.Transpose(Application.Transpose(.Range(.Cells(1, 1), .Cells(1, LastColumn)).Value)) 'This returns the array as single dimension rather than 2D
    End With
End Function

---

As an example, below is some sample 'Test' code to show the concept of using this function .

You could call it like so, and loop through each element perhaps comparing another arrays elements to the correct order elements and do something when the correct order value is found.

Sub TestSub()

    Dim CorrectOrderArray As Variant
    Dim TargetCorrectOrderElement As Variant
    Dim RandomOrderArray As Variant
    Dim TargetRandomOrderElement As Variant
 
    CorrectOrderArray = CorrectOrderHeadingsArrayFunction(Workbooks("Export (4).csv"))  'Change this to target your correct workbook
    RandomOrderArray = Sheet1.Range("A1:AZ1000")  'Change this to target the correct range for your data.
    
    For Each TargetCorrectOrderElement In CorrectOrderArray
        For TargetRandomOrderElement = LBound(RandomOrderArray) To UBound(RandomOrderArray)
            If RandomOrderArray(TargetRandomOrderElement) = TargetCorrectorderValue Then
                'Do some code to write that column to your worksheet
                Exit For  'Leaves the current iteration of the random order array loop to go to the next iteration of the correct order array
            End If
        Next TargetRandomOrderElement
    Next TargetCorrectOrderElement
End Sub