根据列标题重新排列列-运行时错误9:下标超出范围

Question

我想按照列标题重新排列列。我在这个平台上找到了下面的宏。

我在这行代码得到错误。

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

Answer 1

问题是当你的Base工作表12列。

例如工作表有13列。
变量设置为13。
传入col变量，当它达到13并且在correctOrder(col - 1)中使用时，会产生下标out of range错误。
数组有12个元素(从零开始)，所以当它试图找到第13个元素时，它超出了范围。

简单的修复方法是硬编码lastCol值。

With mainWS
lastCol = 12
Set headerRng = .Range(.Cells(1, 1), .Cells(1, lastCol))
End With