| Author |
 Topic  |
|
|
Smilik305
Germany
6 Posts |
 Posted - 09/13/2017 : 11:23:05 AM
|
Hello!
I am trying to make a script with which I can divide the first column and then every third column by a value from the column of another workbook, so that the first column will be divided by the first value of the column, then the forth column by its second value and so on. The result must then be inserted into the third workbook, which is just a copy of the first workbook, but the first and then every third columns are changed accordingly. Here is the script I came up with:
int aa;
int ii;
int N=10;
int NN=1+(N-1)*3;
for (aa=1; aa <= NN; aa += 3)
{
range xx=[Book1]sheet1!col($(aa));
range zz=[Book3]sheet1!col($(aa));
loop (ii, 1, N) {
range yy=[Book2]sheet1!cell($(ii),2);
};
zz=xx/yy;
};
It seems that the internal "loop" loop does not work. Could you please help me how I can nest a loop inside another one, so that the aforementioned desired logics holds.
|
Edited by - Smilik305 on 09/13/2017 11:31:52 AM |
|
|
nick_n
Finland
125 Posts |
|
|
nick_n
Finland
125 Posts |
Posted - 09/13/2017 : 3:01:04 PM
|
However, your case is easy to fix, just replace your:
range yy=[Book2]sheet1!cell($(ii),2);
by:
range yy=[Book2]sheet1!2;
double dYY=yy[ii];
...
...
zz=xx/dYY;
Usually, I'm using subrange like:
range rr=[Book1]Sheet1; //declared outside
loop(aa, 1, 2)
{
range rr1=%(rr)!col($(aa)); //subrange declared inside of scope
...
}
Best,
Nikolay |
 |
|
|
nick_n
Finland
125 Posts |
Posted - 09/13/2017 : 3:13:11 PM
|
Sorry, replace square brackets with:
dYY=yy(ii);
Nikolay |
|
|
|
Smilik305
Germany
6 Posts |
Posted - 09/14/2017 : 09:51:44 AM
|
Hello Nikolay,
Thank you for the fast reply!
I modified the code as you suggested:
int aa;
int ii;
int N=10;
int NN=1+(N-1)*3;
for (aa=1; aa <= NN; aa += 3)
{
range xx=[Book1]sheet1!col($(aa));
range zz=[Book3]sheet1!col($(aa));
loop (ii, 1, N) {
range yy=[Book2]sheet1!2;
double dYY=yy(ii);
zz=xx/dYY;//if this expression goes after the curly bracket, the
result does not change.
};
};
It got better, but now all the columns of the Book1 are divided by the same first value from the column 2 of the Book2, and I need that each next column from the Book1 is divided by the next value from the column 2 of the Book2. In other words, one needs that the "aa" and "ii" counters change simultaneously after each run through the script. However, "ii" remains 1 all the time. Do you know how to do that? |
|
|
|
nick_n
Finland
125 Posts |
Posted - 09/14/2017 : 2:03:34 PM
|
Hi, I didn't catch script idea yesterday.
Hope that could help you:
int ii, iCount=0;
window -a book1; //source book is active now
worksheet -da book3; //makes duplicate of original data to book3
loop(ii, 1, wks.nCols)
{
double tmp1=(ii-1)/3;
int tmp2=(ii-1)/3;
if (tmp1==tmp2) //every third column in book1
{ //will be replaced with new value
iCount++;
range xx=[Book3]sheet1!col($(ii));
range yy=[Book2]sheet1!2;
double dYY=yy(iCount);
xx=xx/dYY;
}
}
Nikolay |
|
|
|
Smilik305
Germany
6 Posts |
Posted - 09/15/2017 : 06:27:49 AM
|
Hello Nikolay,
Yes, it works. Thank you very much, especially for the trick with iCount++. Also, it's important to make a column 2 to be x.
|
Edited by - Smilik305 on 09/15/2017 06:29:08 AM |
|
|
| |
Topic |
|
|
|
Nesting a loop inside of another loop