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?

I've tried using x(formerly colNum) using the loop itself to delete the columns but that doesn't work either. — alistair_Welles
– alistair_Welles, Commented Mar 17, 2016 at 14:29
What are you trying to do? AFAIK, you can't do a Next x in a loop like that. If you don't need anything to happen if the column is those values, then why have a code there? — BruceWayne
– BruceWayne, Commented Mar 17, 2016 at 14:29
You can't "NEXT colNum", it's not a part of any loop, it's just an integer you declared and gave a value to. — Davy C
– Davy C, Commented Mar 17, 2016 at 14:33
Are you saying if the column titles are any where you have next x, that you DONT want to delete? So any column with another header, should be deleted? As @DavyC points out, you try next colNum, but that's not in the loop at all. What's the general purpose of this supposed to be? — BruceWayne
– BruceWayne, Commented Mar 17, 2016 at 14:37
What is colNum, it's not defined? I think you'll need colNum2=colNum+1 instead of next colNum — Nathan_Sav
– Nathan_Sav, Commented Mar 17, 2016 at 14:40

CyberClaw · Accepted Answer · 2016-03-17 15:14:00Z

3

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

edited Mar 17, 2016 at 15:14

answered Mar 17, 2016 at 14:44

CyberClaw

4553 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Scott Craner Over a year ago

This will not work as it will skip numbers, When you artificially increase x by one the next will do it again so the artificial increase gets skipped. For example if we are in the first iteration x = 1 then on the else if x be comes 2 then when the next fires the next loop x becomes 3 before the loop starts again. If you are going to do it this way change to a Do loop instead.

CyberClaw Over a year ago

Indeed, it will increase twice. Yet that seems like what the original poster wanted since he had 2 Nexts, one inside the Ifs, and another at the end of the For cycle. That said if he only wants to increment once, he should use a Do Loop. I'll edit ,my answer for the chance as well.

BruceWayne Over a year ago

You could always leave the x=x+1 in the If parts, and then after End If, just do x = x-1? Then the next x will be the correct one.

CyberClaw Over a year ago

Yeah but why do it like that if you have the Do While cycle for that purpose? For is for the Next.

user4039065 · Accepted Answer · 2016-03-17 16:56:22Z

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.}

Davy C · Accepted Answer · 2016-03-17 14:53:16Z

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

Collectives™ on Stack Overflow

Increment a For loop inside an If statement -VBA

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related