时间戳

首页 > 解决方案 > 有没有办法在循环中每次使用不同的预定义数组

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

问题描述

我有一个从网上获得的子程序，它将以预定义的方式排列工作表列。它适用于单张纸，但现在我有一个应用程序，我需要重新排序 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
'''

标签： excelvba

解决方案

此行不会产生数组：

colOrdr = colOrdr & shCount ' Assign the array

这就是为什么在For indx.

我猜你想以某种方式循环通过 sheetOrdr1 .. sheetOrdr3？如果是这样，试试这个：

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

推荐阅读