使用列表重新排序列。

Question

我想重新排序excel中的一些列，使用M1到M10中的值列表，这些值具有标头的名称和应该排序的顺序。

我有一个脚本，如果我使用：

v = Array("First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country")

但是当我改变到

v = Range("m1:m10").Value

它不起作用，给出了以下信息：

下标超出范围。

以下是整个代码：

Sub Reorganize_columns()
' Reorganize Columns Macro
'
' Developer: If you want to know, please contact Winko Erades van den Berg
' E-mail : winko at winko-erades.nl
' Developed: 11-11-2013
' Modified: 11-11-2013
' Version: 1.0
''v = Array("First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country")
' Description: Reorganize columns in Excel based on column header

Dim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim iNum As Long


v = Range("m1:m10").Value
For x = LBound(v) To UBound(v)
findfield = v(x)
iNum = iNum + 1
Set oCell = ActiveSheet.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

If Not oCell.Column = iNum Then
Columns(oCell.Column).Cut
Columns(iNum).Insert Shift:=xlToRight
End If
Next x
End Sub

Answer 1

您需要findfield = v(x,1)，因为来自范围的数组将自动是2D的。

Answer 2

我宁愿使用集合而不是数组，但是如果您愿意，可以这样赋值：

Dim i As Long

ReDim v(0 To 0)
For i = 0 To 9
    ReDim Preserve v(0 To i)
    v(i) = Range("m1").Offset(i)
Next i

Answer 3

当您使用ARRAY()函数时，您将生成一个基于0的数组.如果您的代码使用此函数生成v，则替换以下一行代码：

v = Array("First Name", "Middle Name", "Last Name", "Date of Birth", "Phone Number", "Address", "City", "State", "Postal (ZIP) Code", "Country")

使用这5行代码：

Dim v(0 To 9) As String, i As Long

    arr = Range("M1:M10")
    For i = 0 To 9
        v(i) = arr(i + 1, 1)
    Next i