Loop to assign the cells with value from a different sheet but in the form of arrays

user_18468858 0
Viewed 78

I'm trying to write a loop 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. I want a loop so I don't have type 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)

The loop I created.

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
vba excel
2 Answers

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

user_42791550

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.

user_104585140
Related