有没有办法每次在循环中使用不同的预定义数组

Question

我有一个从网上得到的子，它将以预定义的方式排列工作表列。它适用于一张纸，但现在我有一个应用程序，我需要重新排序 4 张纸，每张纸的顺序都不同。 此代码使用列标题数组来背弃列 (colOrdr(。我的想法是为每个工作表定义一个数组，然后每次通过循环"交换"数组。(colOdr1， colOdr， ...(但是我得到的类型不匹配。我知道我错误地分配了 var colOrdr，但我无法弄清楚如何正确执行此操作。

Sub ColOrder()
Dim search As Range
Dim cnt As Integer
Dim colOrdr As Variant
Dim sheetOrdr1 As Variant
Dim sheetOrdr2 As Variant
Dim sheetOrdr3 As Variant
Dim indx As Integer
' Define column order for each sheet using their column header names
sheetOrdr1 = Array("ID", "Fname", "Lname", "Addr1", "Addr2", "City", "State", "Zip")
sheetOrdr2 = Array("ID", "Hphone", "Cphone", "Fax", "Other")
sheetOrdr3 = Array("ID", "Sdate", "Edate", "Active", "Rate", "Status", "Cert")
Dim shCount As Integer
shCount = 1
For shCount = 1 To 3
ThisWorkbook.Worksheets(shCount).Select ' Select worksheets left to right
colOrdr = colOrdr & shCount ' Assign the array
cnt = 1
For indx = LBound(colOrdr) To UBound(colOrdr) ' I GET A TYPE MISMATCH ON THIS LINE
Set search = Rows("1:1").Find(colOrdr(indx), LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
If Not search Is Nothing Then
If search.Column <> cnt Then
search.EntireColumn.Cut
Columns(cnt).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
cnt = cnt + 1
End If
Next indx
Next shCount
End Sub
'''

Answer 1

@rskar提出的答案指出了问题和解决问题的方法。 我可能会建议这样的事情：

Sub ColOrder()
Dim search As Range
Dim cnt As Integer
Dim colOrdr As Variant
Dim indx As Integer
Dim shCount As Integer
For shCount = 1 To 3
ThisWorkbook.Worksheets(shCount).Select ' Select worksheets left to right
' Define column order for each sheet using their column header names
Select Case shCount
Case 1
colOrdr = Array("ID", "Fname", "Lname", "Addr1", "Addr2", "City", "State", "Zip")
Case 2
colOrdr = Array("ID", "Hphone", "Cphone", "Fax", "Other")
Case 3
colOrdr = Array("ID", "Sdate", "Edate", "Active", "Rate", "Status", "Cert")
End Select
cnt = 1
For indx = LBound(colOrdr) To UBound(colOrdr)
Set search = Rows("1:1").Find(colOrdr(indx), LookIn:=xlValues, LookAt:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
If Not search Is Nothing Then
If search.Column <> cnt Then
search.EntireColumn.Cut
Columns(cnt).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
cnt = cnt + 1
End If
Next indx
Next shCount
End Sub

代码更紧凑一些，因为减少了变量的数量。

Answer 2

此行不生成数组：

colOrdr = colOrdr & shCount ' Assign the array

这就是为什么人们在For indx时得到类型不匹配的原因。

我猜你想以某种方式循环遍历 sheetOrdr1 .. sheetOrdr3？如果是这样，请尝试以下操作：

colOrdr = Choose(shCount, sheetOrdr1, sheetOrdr2, sheetOrdr3) ' Assign the array