Compile Error: For Control Variable Already in Use

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Can you post your code?
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,023
Office Version
  1. 2010
Platform
  1. Windows
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:

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
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
?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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?
Code:
For Each Row In BB

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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top