Need help writing a Loop in VBA-CodePudding

This is the code I'm trying to write a loop on.

-In laymen's term, I'm basically trying to assign the cells with value from a different sheet but in the form of arrays.

Counter refers to current row in current sheet.
Same goes for iRow but for another sheet.
The value refers to the columns.
Thus, the code below is assigning a cell on the current sheet with value from another sheet's cell.
Right now I'm manually typing out this statements to assign the values and that's why I want to write a loop so I don't have to keep typing the same box of statement every time.

mArray(counter, 25) = dArray(iRow, 18)
mArray(counter, 26) = dArray(iRow, 19)
mArray(counter, 27) = dArray(iRow, 20)
mArray(counter, 28) = dArray(iRow, 21)
mArray(counter, 29) = dArray(iRow, 22)
mArray(counter, 30) = dArray(iRow, 23)
mArray(counter, 31) = dArray(iRow, 24)
mArray(counter, 32) = dArray(iRow, 25)
mArray(counter, 33) = dArray(iRow, 26)
mArray(counter, 34) = dArray(iRow, 27)
mArray(counter, 35) = dArray(iRow, 28)
mArray(counter, 36) = dArray(iRow, 29)
mArray(counter, 37) = dArray(iRow, 30)
mArray(counter, 38) = dArray(iRow, 31)
mArray(counter, 39) = dArray(iRow, 32)
mArray(counter, 40) = dArray(iRow, 33)
mArray(counter, 41) = dArray(iRow, 34)
mArray(counter, 42) = dArray(iRow, 35)

Below is the loop I created, but I doesn't work.

  Dim a As Integer, b As Integer
                    
For a = 25 To 42
For b = 18 To 35
                    
mArray(counter, a) = dArray(iRow, b)

Next b
Next a

Please do advice.

CodePudding user response：

Congratulations.
You have, by accident, discovered a special kind of loops: the nested loops (where one loop is inside the other).

Let me show you what you have programmed:

For a = 25 To 42
  For b = 18 To 35
    do_something(a,b)
  Next b
Next a

When you launch this, this is what gets executed:

do_something(25,18)
do_something(25,19) ' inner loop increases
do_something(25,20) ' inner loop increases
do_something(25,21) ' inner loop increases
do_something(25,22) ' ...
do_something(25,23)
do_something(25,24)
do_something(25,25)
do_something(25,26)
do_something(25,27)
do_something(25,28)
do_something(25,29)
do_something(25,30)
do_something(25,31)
do_something(25,32)
do_something(25,33)
do_something(25,34)
do_something(25,35) ' inner loop is finished, now let's increase the outer loop

do_something(26,18)
do_something(26,19)
do_something(26,20)
...
do_something(26,35)
do_something(26,18)
do_something(26,19)
...
do_something(26,35)
do_something(27,18)
...
(up to:)
do_something(42,35)

As you see, in case of nested loops, you run through the inner loop. Once this one is done, you go to the next item in the outer loop, and the whole inner loop gets executed again, ..., and so it goes until the outer loop has been finished and the inner loop, well, x amount of times :-)

This, obviously, is not what you want: in your case, there's a simple relation between a and b: a = b 7

So, this is what you can do:

For a = 25 To 42
  do_something(a,a - 7)
Next a

Or:

For b = 18 To 35
  do_something(b   7,b)
Next b

Good luck

CodePudding user response：

What you did here is a loop, in another loop. So you code will do this :

a = 25
b = 18 -> 35
...
a = 26
b = 18 -> 35

Which is not what you want. I would tell to do this instead :

For i = 0 to x
    mArray(counter, a   i) = dArray(iRow, b   i)
Next i

In other terms, just do a single loop from 0 to x, then call your method by adding the value of i to both a and b variables. In that way your code will do :

i = 0
a = 25
b = 18
---
i = 1
a = 26
b = 19
---
...
---
i = 10
a = 35
b = 28

Hope this helps. Feel free to ask if something is unclear to you.