Saving multiple variables in a loop

[Rick0825]

Can someone help with getting my code right. I think im close but need a nudge. I have a spreadsheet that I am trying to find a list of words using the find feature in excel. When I find the word, the number I need is 1 cell below it. The part I need help with is saving the data or number im after to a variable. Im trying to loop through. There could be as many as 25 variables on average but this example there is only 6. My example code:

Code: Select all

Let>data=0
Let>k=0
Repeat>k
  Let>k=k+1
  Press Ctrl
  Wait>0.5
  Send>f
  Wait>0.5
  Release Ctrl
  Wait>0.5
  Send>Var1
  Wait>0.5
  Press Enter
  Wait>0.5
  Press Esc
  Wait>0.5
  Press>Vert1 * Num1
  Wait>0.5
  Let>data=%data% %k%
  XLGetSelectedCell>xlBook,data,intRow,intCol
Until>k=6

It loops through finding the word and moving down a cell just fine but I dont know how to save each to a variable: data1...data2...data3...data4...data5...data6

[Dorian (MJT support)]

Have you tried this? :

Code: Select all

    XLGetSelectedCell>xlBook,data_%k%,intRow,intCol

It'll then be in an array.

You don't need this line :

Code: Select all

  Let>data=%data% %k%

Then to output the data you can do something like this :

Code: Select all

ArrayCount>data,count
let>OutputLoop=0
repeat>OutputLoop
  Let>OutputLoop=OutputLoop+1
  MDL>data_%OutputLoop%
Until>OutputLoop,count

Here's a standalone version to demonstrate :

Code: Select all

Let>k=0
Repeat>k
  Let>k=k+1
  /*
    Press Ctrl
    Wait>0.5
    Send>f
    Wait>0.5
    Release Ctrl
    Wait>0.5
    Send>Var1
    Wait>0.5
    Press Enter
    Wait>0.5
    Press Esc
    Wait>0.5
    Press>Vert1 * Num1
    Wait>0.5
    XLGetSelectedCell>xlBook,data_%k%,intRow,intCol
  */

  let>data_%k%=Test%k%
Until>k=6


ArrayCount>data,count
let>OutputLoop=0
repeat>OutputLoop
  Let>OutputLoop=OutputLoop+1
  MDL>data_%OutputLoop%
Until>OutputLoop,count

[Rick0825]

Thanks Dorian!

Now I am on to looping back to get more data from my spreadsheet. Does this look like it would work?

Code: Select all

Let row=0
Repeat>row
	Let>row=row+1
	Let>k=0
	  Repeat>k
	    Let>k=k+1
	    XLGetSelectedCell>xlBook,data_%k%,intRow,intCol
	    Let>stored_%k%=data_%k%
	    Wait>0.5
	    Press Right
	    Wait>0.5
	  Until>k=4
  Wait>0.5
  Press Down
  Wait>0.5
  Press Left * 4
Until>row=2

ArrayCount>stored,count
let>Loop=0
Repeat>Loop
   Let>Loop=Loop+1
   MDL>stored_%Loop%
Until>Loop,count

What happens is the 2nd loop back to get another row of data is the only data show in the message. I think the second pass of data is overriding the first set. If this is true then how do I store all 8 cell variables? I have used ini files but was hoping for an easier solution.

[Rick0825]

Only took me about 8 hrs but I figured it out.

Code: Select all

Let>kCount=4
Let>row=0
Let>k=0
  Repeat>row
  
   Repeat>k
    Let>k=k+1
    XLGetSelectedCell>xlBook,data_%k%,intRow,intCol
    Let>stored_%k%=data_%k%
    Wait>0.5
    Press Right
    Wait>0.5
    Until>k=%kCount%
       
    Press Down
    Wait>0.5
    Press Left * 4
    Let>kCount=kCount+4
    Let>row=row+1
     
  Until>row=%NumCount2%

ArrayCount>stored,count
let>Loop=0
  Repeat>Loop
   Let>Loop=Loop+1
   MDL>stored_%Loop%
  Until>Loop,count