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

excelos

Well-known Member
Joined
Sep 25, 2011
Messages
591
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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
 
Upvote 0
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!
 
Upvote 0
That is exactly what I would expect.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

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
Back
Top