我正在尋找一個用于根據標題列重新排列列的宏。我在這個平臺上找到了下面的宏。當我嘗試運行此宏時，它作業正常，但最后我在此行代碼處收到錯誤。
If cel.value=correctOrder(col - 1) Then
這是錯誤
“運行時錯誤‘9’：下標超出范圍
下面是宏
Sub rearrange_Cols()
Dim correctOrder() As Variant
Dim lastCol As Long
Dim headerRng As Range, cel As Range
Dim mainWS As Worksheet
Set mainWS = ActiveWorkbook.Worksheets("Base")
' Edit this to be the correct order you need
correctOrder() = Array("FT Id", "FT Name", "FT Primary Location", "Deployed Location", " Approval Status", "Approved By", " Approved on Date", "FT acceptance Status", "Skill", " Billed", "Date of Joining"," Year" )
' Now, we know that the number of headers you have to rearrange are `UBound(CorrectOrder) 1`
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 = ActiveWorkbook.Sheets.Add
newWS.Name = "New Open"
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
uj5u.com熱心網友回復：
問題是當您的基本作業表有超過12列時。
例如，如果作業表有 13 列。
lastCol變數設定為 13。
它被傳遞到col變數中，當它達到 13 并用于correctOrder(col - 1)下標超出范圍錯誤時會生成。
correctOrder陣列有 12 個元素（從零開始），所以當它試圖找到第 13 個元素時，它超出了范圍。
簡單的解決方法是硬編碼 lastCol 值。
With mainWS
lastCol = 12
Set headerRng = .Range(.Cells(1, 1), .Cells(1, lastCol))
End With
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/476957.html
上一篇：ExcelVBA：創建一個嵌套回圈并為范圍內的每個值保存輸出
下一篇：從平面陣列創建陣列層次結構