What are the available ways to create Nested Loops in VBA?

excelos

Active Member
Joined
Sep 25, 2011
Messages
375
Office Version
  1. 365
Platform
  1. Windows
Hello

I want to create this nested loop:

For Each cell In Range("a1:a10")
Cell.value = 1*(For i = 0 to 10 next i)
Next cell

Any idea how it could be structured?

Also are there any other forms/types of nested Loops and what are the possible structures?

Thanks!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,609
Office Version
  1. 365
Platform
  1. Windows
You can do that like
VBA Code:
Sub excelos()
   Dim Cl As Range
   Dim i As Long
   
   For Each Cl In Range("A1:A10")
      For i = 0 To 10
         Cl.Value = 1 * i
      Next i
   Next Cl
End Sub
For other kinds of loops, just search the net for VBA Loops
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
375
Office Version
  1. 365
Platform
  1. Windows
You can do that like
VBA Code:
Sub excelos()
   Dim Cl As Range
   Dim i As Long
  
   For Each Cl In Range("A1:A10")
      For i = 0 To 10
         Cl.Value = 1 * i
      Next i
   Next Cl
End Sub
For other kinds of loops, just search the net for VBA Loops

Thanks, I had written something that appears to be identical as yours:

For Each cell In Range("a1:a10")
For i = 0 To 10
cell.Value = 1 * i
Next i
Next cell

But I get all the cells in A1:A10 as 10.
That is not expected!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,609
Office Version
  1. 365
Platform
  1. Windows
That is exactly what I would expect.
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
375
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Shouldn't it be:

1
2
3
4
5
6
7
8
9
10

??
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,508
Office Version
  1. 365
Platform
  1. Windows
Shouldn't it be:

1
2
3
4
5
6
7
8
9
10

??
That looks like you just need a single loop like this.
VBA Code:
Sub FillSequence()
  Dim i As Long
  
  For i = 1 To 10
    Cells(i, 1).Value = i
  Next i
End Sub
 

excelos

Active Member
Joined
Sep 25, 2011
Messages
375
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks, this gives technically the same result but it's different.

Let me rephrase the problem:

I want it to go to the first cell in range, use i = 5 and populate 100*i.
Then go to the next cell in range, use i=6 and populate 100*i.
And so on until the last cell in the range, where it will populate it with 1*15 (since there are 10 cells in the range).

How can that be achieved with a *nested* loop?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,050
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Maybe...
VBA Code:
Sub FillSequence()
  Dim i As Long
  
  For i = 1 To 10
    Cells(i, 1).Value = (i + 4)*100
  Next i
End Sub

Last cell would be i * 14 if there are 10 cells
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,609
Office Version
  1. 365
Platform
  1. Windows
How can that be achieved with a *nested* loop?
Why do you want to use a nested loop, when there is no need? Loops tend to be slow so should be avoided where possible.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,508
Office Version
  1. 365
Platform
  1. Windows
It does not make sense to use nested loops. You have 10 cells and you want to fill each cell with one value. That is a total of 10 values.
If you loop through 10 cells and loop i through 10 values then you are doing something 100 times but still only have 10 cells to fill.

Yet another single loop would be
VBA Code:
Sub FillSequence2()
  Dim i As Long
  Dim cell As Range
  
  i = 4
  For Each cell In Range("A1:A10")
    i = i + 1
    cell.Value = 100 * i
  Next cell
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,659
Messages
5,637,622
Members
416,977
Latest member
kdoederlein

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