i was looking for a macro for rearranging columns as per header columns. I have found the below macro on this platform. When i tried to run this macro, it is working fine but towards the end I am getting the error at this line code.

If cel.value=correctOrder(col - 1) Then

This is the error

"Run-time error '9': Subscript out of range

Below is the macro

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

CodePudding user response：

The issue is when your Base worksheet has more than 12 columns.

For example if the worksheet has 13 columns.
The lastCol variable is set to 13.
It is passed into the col variable and when it reaches 13 and is used in correctOrder(col - 1) a Subscript out of range error is generated.
The correctOrder array has 12 elements (zero based), so when it tries to find the 13th, it is out of range.

The simple fix is to hard code the lastCol value.

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