Excel VBA For Loop Error

Question

I'm trying to run a simple For loop which will be expanded to include more functionality later but having trouble as it keeps throwing an error "invalid next control variable reference". The code I am trying to use is listed below.

Sub Tickbox()

Set Location = Sheets("TickBoxSheet").Range("B:B")

i = WorksheetFunction.CountA(Location)

Sheets("TickBoxSheet").Range("B2").Select


For a = 1 To i
    If Selection.Value = "True" Then
        Row = Selection.Row
        'Hide some rows in another sheet via if statements

        ActiveCell.Offset(1, 0).Select
    End If
Next i

End Sub

I don't know if I need more coffee this morning but I can't seem to figure out what the hell is going on. Any help will be greatly appreciated.

While specifying the loop variable in a Next statement may improve performance, any gain is infinitesimal. In general, unless you have many nested loops I'd recommend against the practice, if only to avoid the problem you had here. — Mike Woodhouse
– Mike Woodhouse, Commented Jul 4, 2012 at 8:52

Ilmo Euro · Accepted Answer · 2012-07-04 08:35:27Z

8

The incremented variable (in Next) should be the index variable, i.e.:

For a = 1 To i
  '...
Next a

i is so popular as index that you should think twice before using it in other contexts.

answered Jul 4, 2012 at 8:35

Ilmo Euro

5,3351 gold badge29 silver badges29 bronze badges

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

2 Comments

Siddharth Rout Over a year ago

+ 1 Too fast for me. Abandoning my post LOL

SomethingSmithe Over a year ago

Headdesk Yup Definitely need more coffee... Thanks!

Siddharth Rout · Accepted Answer · 2012-07-04 08:52:58Z

You have already got your answer from llmo. However there are few other things I would like to stress upon...

Try and avoid .Select. It will slow down your code.

Also It is not necessary that WorksheetFunction.CountA(Location) will give you the last row considering that you want to loop through all the rows which have data. I suggest this

Sub Tickbox()
    Dim i As Long, a As Long, Rw As Long

    With Sheets("TickBoxSheet")
        i = .Range("B" & .Rows.Count).End(xlUp).row

        For a = 2 To i
            If .Range("B" & a).Value = "True" Then
                Rw = a
                'Hide some rows in another sheet via if statements
            End If
        Next a
    End With
End Sub

You can make it more fast using Autofilter as well so that you loop through cells which only have True For example

Sub Tickbox()
    Dim i As Long, a As Long, Rw As Long
    Dim Location As Range, acell As Range

    With Sheets("TickBoxSheet")
        '~~> Remove any filters
        .AutoFilterMode = False

        i = .Range("B" & .Rows.Count).End(xlUp).row

        With .Range("B1:B" & i)
            .AutoFilter Field:=1, Criteria1:="True"
            Set Location = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
            Debug.Print Location.Address
        End With

        '~~> Remove any filters
        .AutoFilterMode = False

        For Each acell In Location
            If acell.Value = "TRUE" Then
                Rw = acell.row
                'Hide some rows in another sheet via if statements
            End If
        Next acell
    End With
End Sub

Collectives™ on Stack Overflow

Excel VBA For Loop Error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related