For control variable already in use vba ошибка

Добрый день, уважаемые форумчане.
Получил в наследство на новом рабочем месте «почти готовый файл». Он, конечно, многое решает в работе. Но есть, по крайней мере, один косяк. Коллега посоветовал к Вам обратиться.
Одна из форм никак открывается. Я пробовал её подогнать по образцу других форм, но не смог. А в работе она совсем не лишняя.
Вот, пришёл к Вам за помощью.
В приложенном примере при запуске формы с кнопки появляется окно

Compile error:
For control variable already in use А в коде формы выделяется For i = 2 To

Причём во вроде бы аналогичной нормальной, рабочей форме (которую я удалил в примере) такой же фрагмент используется и вроде комбобокс точно такой же, и она работает.
А та, что оставлена в примере, не работает.
Знатоки, пожалуйста, помогите запустить форму.

Источник

Permalink

Cannot retrieve contributors at this time

title	keywords	f1_keywords	ms.prod	ms.assetid	ms.date	ms.localizationpriority
For control variable already in use	vblr6.chm1011174	vblr6.chm1011174	office	9b817917-5156-7dc6-f4f1-4fc6626ad5c9	06/08/2017	medium

When you nest For…Next loops, you must use different control variables in each one. This error has the following cause and solution:

An inner For loop uses the same counter as an enclosing For loop. Check nested loops for repetition. For example, if the outer loop uses For Count = 1 To 25, the inner loops can’t use Count as the control variables.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

Источник

This particular use of a nested «For» is something that I did not expect. I have trimmed it down to a very simple example. The outer For loop works perfectly using the member of the Type «abc», but the enclosed For statement produces the compiler error. I’ve hunted for an explanation, but so far have found none.

Option Explicit

Private Type abc
    i As Integer
    j As Integer
End Type

Private Sub CommandButton1_Click()
    Dim dog As abc
    Dim cat As abc
    
    For dog.i = 1 To 10
        For cat.i = 5 To 9
        
        Next
    Next
End Sub

asked Jan 23, 2021 at 20:20

According to this documentation, nested for loops must use different variables for their control counter. And according to this documentation, Type elements follow the same rules as variables.

Element names also follow standard variable naming conventions, except that keywords can
be used.

I suppose there must be something about the struct (Type) that when you reference one of its elements, it uses that as a named variable of sorts. So because your two control abcs have an element named i, the for loop is saying «hey, there is already another for loop above me using a control variable named i«. Notice you won’t get the same issue if you use dog.i for the outer loop, and cat.j for the inner.

As for solutions to this, you could store the values of dog.i and cat.i into a separate variable to use with this loop structure:

Private Sub CommandButton1_Click()
    Dim dog As abc
    Dim cat As abc
    Dim x As Integer
    Dim y As Integer
    
    x = dog.i
    y = cat.i
    
    For x = 1 To 10
        For y = 5 To 9

        Next
    Next
End Sub

answered Jan 23, 2021 at 20:37

ArcherBirdArcherBird

2,01910 silver badges35 bronze badges

I had wanted to determine if the error I was seeing was a VBA limitation (flaw) or whether there was some Setting or Option that I did not know, that would be appropriate.
I have come up with a work-around that is acceptable for what I need to do.

Option Explicit

Private Type abc1
    i As Byte
    j As Byte
End Type

Private Type abc2
    i As Byte
    j As Byte
End Type

Private Sub CommandButton1_Click()
    Dim dog As abc1
    Dim cat As abc2
    
    For dog.i = 1 To 10
        For cat.i = 5 To 9
        
        Next
    Next
End Sub

answered Jan 24, 2021 at 18:11

Источник

Greetings,

I am receiving the compile error «For Control Variable Already in Use».

I have nested For Each Next loops with the Control Variable as «Row». I am looping by the row with a predefined range of rows. How can I maintain the nested loop structure and use a variable that loops using a row without using the «For Each Row in CC» structure?

Thanks!

Why does 9 mean SUM in SUBTOTAL?

It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

How can I maintain the nested loop structure and use a variable that loops using a row without using the «For Each Row in CC» structure?

Presumably, you wrote something of the form:

Rich (BB code):

For Each row In CC
    For Each row In CC
        [....]
    Next row
Next row

In that context, «row» is a variable name. And by the way, «row» is a poor name to use for a variable because it is also a keyword — a special VBA word like «if», «for», «next», etc.

(VBA can usually tell the difference by context. But it is still considered «poor programming practice».)

Choose different control variable names for each nest for-loop. For example:

Rich (BB code):

For Each r1 In CC
     For Each r2 In CC
         [....]
     Next r2
Next r1

(I like brief control variables. But you might prefer row1 and row2 instead of my r1 and r2.)

Note that the order of control variable names in the Next statements is the opposite of the order in the For statements.

In other words, we always loop innermost for-loops first.

Last edited: Nov 26, 2017

Thanks for the reply!

Here is the upper portion of the nested loops:

Code:

For Each Row In CC        
        H = Range(Cells(E, "A")).Row
        BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A")).Row
                      
        With PA.ws
              
            For Each Row In BB

Initially I had integer variables in place of «Row», but received an error «variant or object required». When you assign the control variable r1 and r2, what types of variables are these? Range?

If CC and BB are ranges any loop control variable you use with them will also be a range and should be declared as such.

Will the machine know to step from the G row variable to the I row variable in .Range(Cells(G, «A»), Cells(I, «A»)).Row when

Code:

Set R2 = .Range(Cells(G,"A")).Row

Why do you have .Row in the below

Code:

BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))[COLOR="#FF0000"].Row[/COLOR]

which changes it from a Range to a number when you have it looping through a range below?

and below you are using Set which only applies to an Object(in this case a range) when the .Row again changes the Range to a number.

Code:

Set R2 = .Range(Cells(G,"A")).Row

I think you need to post your full code

Источник

excelvba

Im using VBA in Excel.

Im getting an error message saying control variable already in use. When the error pops up the second For Each r In is highlighted in the VBA editor.Can anyone help correcting this code. Thank you

Dim ws As Worksheet
Dim r As Range

For Each ws In Worksheets
    If InStr(1, ws.Name, "Wk", 1) > 0 Then
        For Each r In ws.Range("C118:I124")
        For Each r In ws.Range("C163:J168")
        For Each r In ws.Cell(E2, E15, E28, E41, E54, E67, E80)
            r.Formula = Replace(r.Formula, "Wk1", r.Parent.Name)
        Next r
    End If
Next ws

MsgBox "Done"
End Sub

Related Question

Возможно, вам также будет интересно: