asked on

Rearrange Macro

I have a macro below that rearrange the columns as I indicated. However, I am getting a Can't execute code in break mode error.

Sub ColumnReorder()
Dim arrColOrder As Variant, ndx As Integer
Dim Found As Range, counter As Integer

arrColOrder = Array("PRODUCT ID", "BRAND", "DESCRIPTION", "COLOR", "SIZE", "QTY", "EST. RETAIL", "EST. EXT. RETAIL", "CATEGORY", "SUB-CATEGORY", "ITEM", "IMAGE URL", "ORDER", "PALLET ID", "CONDITION")

counter = 1
Application.ScreenUpdating = False

For ndx = LBound(arrColOrder) To UBound(arrColOrder)
Set Found = Rows("1:1").Find(arrColOrder(ndx), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If Not Found Is Nothing Then
If Found.Column <> counter Then
Found.EntireColumn.Cut
Columns(counter).Insert Shift:=xlToRight
Application.CutCopyMode = False
End If
counter = counter + 1
End If
Next ndx
Application.ScreenUpdating = True
End Sub

Roy Cox

Your code has failed and you need to check what caused it to fail before it can run again you need to press the reset button in the VB Editor to clear the error. It's in the menu ( a blue box) next to the design button (set square & ruler)

Roy Cox

By the way the code that has errorred will be highlighted in yellow. Post an example workbook so we can try to find the error

Norie

Where is the code located and how are you executing it?

Mike Eghtebas

I am making sample application to:
1) Fully understand what you are trying to accomplish. So, please comment if this differs from what you have in mind.

Background: The sample worksheet above has 3 columns each with 3 rows of data as shown (please ignore the 4th column AA).
Objective: In the code window, the array named arrColOrder has switched 2nd and 3rd column so that when the macro executed the worksheet is expected to update the column order per arrColOrder.

Question: Is this what you intend to do?

Mike

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Eghtebas

In the code below, I am trying to do the following:
1- Store user preferred column order (as you had). There are some notes included to streamline and make it easy for user to edit column order right from a worksheet instead of working in code window.
2- Compare number of columns in the arrColOrder and the worksheet to make sure we are working with the same number of columns (arrColOrder has the same number of columns as the worksheet).
3- Insert blank columns A-Z (meaning the existing column A shifts to column AA, column B shifts to AB, and so fort. (not done yet)
4- Now, the code continues to drag the respective columns back to their intended positions per arrColOrder. (not done yet)

Please look to see you like this approach before someone here or I do items 3 and 4 above?

Sub aa()

    Dim intColumnsInCode As Integer         'store number of columns in code (arrColOrder)
    Dim intColumnsWorksheet As Integer      'store number of columns in the worksheet
    Dim strMsg As String                    'store QC note, if none proceed with the operation

    Dim arrColOrder As Variant      'store column names in an array
    Dim ndx As Integer              'array index variable
    Dim Found As Range              'array column names found on the worksheet
    Dim counter As Integer
    Dim i As Integer
    
    'populate arrColOrder array:
    '(Note: Later on write a routine to populate arrColOrder array from a worksheet, say named
    ' Maintenance', where the users could easily modify the order columns should be displayed.)
    arrColOrder = Array("BRAND", "DESCRIPTION", "COLOR")
    intColumnsInCode = UBound(arrColOrder) - LBound(arrColOrder) + 1
    
    'Find number of columns in the worksheet and arrColOrder must match (Note: For a different scenarios,
    'the present code needs to be revised accordingly).
    intColumnsWorksheet = Application.ActiveSheet.Cells.SpecialCells(xlLastCell).Column '- 1
    
    'Update strMsg to inform the user for a corrective action if the number of columns in arrColOrder and the Worksheet do not match.
    strMsg = fn_strMsg_columns(intColumnsInCode, intColumnsWorksheet)
    If strMsg = "" Then
        'add code to shift A:Z to AA:AZ (this means maximum number of columns allowed is 26).
        'this code then, per arrColOrder, finds the matching columns to drag them onto first blank column
    Else
        MsgBox strMsg
    End If
    
    
End Sub
Function fn_strMsg_columns(intCode, intWs) As String

    Dim strTemp As String
    
    If intCode > intWs Then
        strTemp = "'arrColOrder' array has more column names compared to the Worksheet."
    ElseIf intCode < intWs Then
        strTemp = "'arrColOrder' array has less column names compared to the Worksheet."
    End If
    
    If strTemp <> "" Then
        strTemp = strTemp & vbNewLine & _
        "Please make sure the number of columns match."
    End If
    
    fn_strMsg_columns = strTemp
    
End Function

Open in new window

Shyretta Jenkins

ASKER

This worked! Thank you so much.