从列标题创建 VBA 数组? [英] Create a VBA Array from Column Headers?

问题描述

我有一个导出NewExport"总是随机化我收到的数据列.我需要这些列与TheOrder"中的列顺序对齐，因此此代码将有助于重新组织导出以与我已经构建的列标题对齐.

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.

我有 132 列需要重新对齐，虽然我可以全部输入，但必须有一种更简单的方法来与我已经创建的列标题对齐.应该注意的是，下面的代码是从另一个 StackOverflow 答案中无耻地复制/粘贴的.

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

推荐答案

我最近为我的一个项目编写了一个列查找器"功能.

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

我已对其进行了修改以满足以下您的要求.

I've modified it to suit your requirements below.

• 该函数要求您传递正确排序标题所在的工作簿以进行捕获.您可以修改它以需要您的 TargetWorksheet 来代替它，使其更具动态性.
• 该函数返回一维Array.
• 该函数在 Target Worksheet 中查找最后使用的列，允许更改列标题的数量(如您自己的答案中所述，列号已硬编码).

• 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

这篇关于从列标题创建 VBA 数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！