Increment a For loop inside an If statement -VBA

Question

I need to delete columns in a spreadsheet using a loop instead of manually hardcoding those columns in. However all I get is a very unhelpful Next without For error.

Sub test()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

Dim colNum2 As Integer
colNum2 = 1

For x = 1 To 32

    If Range("A1").Value = "Order No." Then
        Next colNum
    ElseIf Range("B1").Value = "Line No." Then
        Next colNum
    ElseIf Range("C1").Value = "Order Qty." Then
        Next x
    ElseIf Range("D1").Value = "PO" Then
        Next x
    ElseIf Range("E1").Value = "Sched Date" Then
        Next x
    ElseIf Range("F1").Value = "Sched MFG Line" Then
        Next x
    ElseIf Range("G1").Value = "Item No." Then
        Next x
    ElseIf Range("H1").Value = "Item Width" Then
        Next x
    ElseIf Range("I1").Value = "Item Height" Then
        Next x
    ElseIf Range("J1").Value = "SL Color" Then
        Next x
    ElseIf Range("K1").Value = "Frame Option" Then
        Next x
    End If
        'Checks if the cell matches a specific string required by the sorter
        'if TRUE should skip through to the next increment of colNum

    Columns(colNum2).EntireColumn.Delete
        'uses the current number of colNum to delete the current column number
    colNum2 = colNum2 + 1

Next x
    'increments colNum by one
    'Iterates next through the loop

I feel like this would work with say Java or Python so I'm really irritated VBA won't let me do this.

Can someone please explain what is going wrong with this code?

Answer 1

It seems to me that you want to delete all of the columns that do not match 'a specific string required by the sorter'. In that case, you could loop through all of the columns header labels, deleting the ones that do not match or use a custom left-to-right sort to put all of the non-matching columns to the right and delete then en masse.

Method 1 - Delete non-matching columns

Sub test1()
    Dim c As Long, vCOLs As Variant

    vCOLs = Array("Order No.", "Line No.", "Order Qty.", "PO", _
                  "Sched Date", "Sched MFG Line", "Item No.", _
                  "Item Width", "Item Height", "SL Color", "Frame Option")

    With Application
        '.ScreenUpdating = False
        '.EnableEvents = False
    End With

    With Worksheets("sheet1")
        With .Cells(1, 1).CurrentRegion
            'delete from right-to-left or risk missing one
            For c = .Columns.Count To 1 Step -1
                If IsError(Application.Match(.Cells(1, c).Value2, vCOLs, 0)) Then
                    .Columns(c).EntireColumn.Delete
                End If
            Next c
        End With
    End With

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

Method 2 - Custom sort, then offset and delete

Sub test2()
    Dim vCOLs As Variant

    vCOLs = Array("Order No.", "Line No.", "Order Qty.", "PO", _
                  "Sched Date", "Sched MFG Line", "Item No.", _
                  "Item Width", "Item Height", "SL Color", "Frame Option")

    With Application
        '.ScreenUpdating = False
        '.EnableEvents = False
        .AddCustomList ListArray:=vCOLs
    End With

    With Worksheets("sheet1")
        With .Cells(1, 1).CurrentRegion
            'custom sort to bring the important fields to the left
            .Cells.Sort Key1:=.Rows(1), Order1:=xlAscending, _
                        Orientation:=xlLeftToRight, Header:=xlNo, _
                        OrderCustom:=Application.GetCustomListNum(vCOLs)

            'offset and delete the unwanted columns
            With .Offset(0, Application.Match(vCOLs(UBound(vCOLs)), .Rows(1), 0))
                .EntireColumn.Delete
            End With
        End With
    End With

    With Application
        .DeleteCustomList .GetCustomListNum(vCOLs)
        .ScreenUpdating = True
        .EnableEvents = True
    End With

End Sub

With either method you are simply listing the columns you want to keep and removing the rest.

---

_{There is a twist between .Cells.Sort.SortFields.Add and .Cells.Sort that usually generates some confusion. The .SortFields.Add method uses a CustomOrder:= parameter and the Range.Sort method uses a OrderCustom:= parameter. The two are most definitely NOT the same but often get used interchangeably with disastrous results.}

Answer 2

Just use var = var + 1 instead of Next. Next ends the For cycle. Also you don't need to repeat the variable name on the Next line since it's already in the For line. (For i = 0 To 5 ... Next)

For x = 1 To 32

    If Range("A1").Value = "Order No." Then
        colNum = colNum +1
    ElseIf Range("C1").Value = "Order Qty." Then
        x = x + 1
    End If
Next

Keep in mind what Scott Cranner said, the Next will also do x=x+1, so if you only want to increment once per cycle, use the Do While cycle instead

x = 1
Do While x <= 32

    If Range("A1").Value = "Order No." Then
        colNum = colNum +1
    ElseIf Range("C1").Value = "Order Qty." Then
        x = x + 1
    End If
Loop

Answer 3

I suspect you are trying to delete columns based on their text values in row 1. This will give you what you want, just put all the text references that you want to delete in the CASE statement.

Option Explicit

Sub DeleteColumns()

    Dim colNum As Integer

    colNum = 1

    Do While Range(alphaCon(colNum) & 1).Value <> ""

        Select Case Range(alphaCon(colNum) & 1).Value

            Case "ColumnIDontWant", "AnotherColumnIDontWant"

            Columns(colNum).EntireColumn.Delete

        End Select


        colNum = colNum + 1

    Loop

End Sub

Public Function alphaCon(aNumber As Integer) As String
' Fixed version 27/10/2011

Dim letterArray As String
Dim iterations As Integer

letterArray = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

    If aNumber <= 26 Then

        alphaCon = (Mid$(letterArray, aNumber, 1))

    Else

        If aNumber Mod 26 = 0 Then

            iterations = Int(aNumber / 26)
            alphaCon = (Mid$(letterArray, iterations - 1, 1)) & (Mid$(letterArray, 26, 1))

        Else

            'we deliberately round down using 'Int' as anything with decimal places is not a full iteration.
            iterations = Int(aNumber / 26)
            alphaCon = (Mid$(letterArray, iterations, 1)) & (Mid$(letterArray, (aNumber - (26 * iterations)), 1))

        End If

    End If

End Function