VBA Arrays - unable to change array size

[CarolynS]

Hi

I'm amending some code written by a colleague who's now left. The code is used to change the column order of some data in excel and the column headers are contained within an array in the code. I've added 1 extra header in to the array but when I run the code it's now doesn't re order all of the columns, there are 2 columns that don't get moved. The NDX variable is only counting to 22 and I need it to count to 23. I tried to resize the array using Dim arrColOrder (1 to 23) as variant but that gave an error (couldn't assig to array) so i then tried Dim arrColOrder () as variant and the ReDim arrColOrder (1 to 23) as variant but that didn't work either. I think the Upper bound needs to be increased but I can't work out how to do it. Code is below. Thanks


Dim arrColOrder As Variant
Dim ndx As Integer
Dim counter As Integer

arrColOrder = Array("Person - Username", "Person - First Name", "Person - Last Name", "Brand", "BU", "Opex Level 2", "Organisation - Name", "Person - Location Name", "Person - Workforce ID", "Person - Job Title", "Person - Start Date", "Person - Status", "Person - E-mail", "Manager - First Name", "Manager - Last Name", "Manager - E-mail", "Certification - Status", "Certification - Acquired On", "Days Overdue", "Person - Franchise ID", "Student Certification - Assigned On", "Person - Custom4")

counter = 1

Application.ScreenUpdating = False



For ndx = LBound(arrColOrder) To UBound(arrColOrder)

Set oFound = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If Not oFound Is Nothing Then
If oFound.Column <> counter Then
oFound.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If

Next ndx

Application.ScreenUpdating = True

 

[MickG]

Could it be that some of the names in your array and the names in your header do not match.
I've just Rearranged your headers From your array along with column Data, Run the code and it seems to work OK.

 

[CarolynS]

Aaargh so obvious - can't believe I missed it! I thought I'd copied and pasted the new headers to avoid that issue but obviously i didn't as there's an extra space in one on my spreadhseet . Thank you

 

[MickG]

You're welcome