For control variable already in use vba ошибка - Ремонт и установка крупной бытовой техники

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

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

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

Источник

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

For control variable already in use

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!

Back into an answer in Excel

Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

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

Источник

На чтение 3 мин. Опубликовано 15.12.2019

Когда вы вложены в. Next в каждом цикле необходимо использовать разные управляющиепеременные . When you nest For. Next loops, you must use different controlvariables in each one. Эта ошибка имеет следующие причину и решение: This error has the following cause and solution:

Во внутреннем цикле for используется тот же счетчик, что и во вложенном цикле for . An inner For loop uses the same counter as an enclosing For loop. Проверьте вложенные циклы на повторы. Check nested loops for repetition. Например, если внешний цикл использует For Count = 1 To 25 , внутренние циклы не могут использоваться Count в качестве переменных управления. For example, if the outer loop uses For Count = 1 To 25 , the inner loops can’t use Count as the control variables.

Для получения дополнительной информации выберите необходимый элемент и нажмите клавишу F1 (для Windows) или HELP (для Macintosh). For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

Поддержка и обратная связь Support and feedback

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Have questions or feedback about Office VBA or this documentation? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь. Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

When i try debugging this code, i am getting two errors, the first one saying «For control variable already in use», and the second one «Expected end Sub». the goal of my code is that i created series of checks in my sheet «poteaux» and i want to take values from the sheet «Efforts poteaux» and put them in certain cells in poteaux so that the formulas will be checked. The msg box gives me if it’s good or not. i am new to vba and im giving it a try.

For control variable already in use

When you nest For. Next loops, you must use different controlvariables 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).

Источник