_ArraySearch doesn't work second time in loop

[Quintillius]

The purpose of the script is to copy the content of a cell in a program to clipboard. This clipboard value is searched in an array by using _ArraySearch. 

This works similar to VLOOKUP in Excel.

The result of the second column is then being pasted in the program's next cell. Then the script continues to the next line doing the same thing again in a loop.

The problem is that the script only works once, for the first line only.

Here's the code:

#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>

Local $oWorkbook = _Excel_BookAttach("C:\Users\Steven\Desktop\Account Sales invoer.xlsx")

Local $x = WinGetHandle("Toevoegen Account Sale data")
Local $y = WinGetHandle("Account Sales invoer")

WinActivate($x)
Local $aArray = _Excel_RangeRead($oWorkbook)
;_ArrayDisplay($aArray, "2D display") ; Debug works fine

For $i = 1 to 3 ; Number of tries

    ; Copy
    ControlSend ($x, "", "", "^c")
    Local $iIndex = _ArraySearch($aArray, ClipGet())
    
    If @error Then MsgBox(0, "Not Found"," Not found--error="&@error)
      
    msgbox(0, "", ClipGet() & " " & $iIndex) ; Debug
    _ArrayToClip($aArray, "", $iIndex, $iIndex, "", 1, 1)

    ; Paste
    Sleep(100)
    ControlSend($x, "", "", "{TAB 2}")
    ControlSend($x, "", "", "v")
    ControlSend($x, "", "", "{BACKSPACE}")
    Sleep(100)
    ControlSetText($x, "", "", ClipGet())
    ControlSend($x, "", "", "{DOWN}")
    ControlSend($x, "", "", "{LEFT 2}")

Next

 

See below results for msgbox:

This one is correct. Value 60840 is found in the array list, returning index 144.

 

Next line gives error 6: value not found? It's in the list though.

The clipboard function works correct as being shown:

Excel file:

Spoiler

55182	16
60703	104
60704	104
60705	1
60706	104
60707	73
60708	3
60709	104
60710	104
60711	104
60712	94
60713	4
55183	17
60715	104
60716	104
60717	104
60718	104
60719	104
60720	40
60721	88
60722	5
60714	200
60723	80
60724	80
60726	90
60727	80
60728	40
60730	12
60731	300
60732	5
60729	106
60725	80
55184	14
60735	104
60736	104
60737	99
60738	104
60739	76
60740	5
60733	104
60734	32
60741	80
60742	80
60743	75
60747	104
60748	24
60749	67
60750	10
60744	100
60745	100
60746	100
55185	2
55186	19
60751	104
60752	104
60757	104
60758	117
60759	12
60753	104
60754	82
60755	80
60756	70
60768	104
60769	110
60770	40
60771	13
60765	104
60766	26
60767	250
60764	75
60760	2
60761	100
60762	100
60763	100
55187	2
55188	25
60774	104
60776	75
60777	90
60778	90
60779	80
60780	80
60781	19
60772	100
60775	38
60773	104
60785	100
60784	100
60783	100
60782	60
60786	80
60787	75
60788	200
60789	80
60790	28
60791	21
55189	3
55190	27
60794	104
60797	104
60798	104
60796	75
60795	118
60799	104
60800	75
60801	59
60802	16
60792	120
60793	50
60809	100
60810	104
60811	104
60812	104
60813	48
60814	72
60815	15
55191	1
60803	75
60804	75
60805	100
60806	100
60807	100
60808	100
55192	3
55193	19
60817	100
60818	100
60819	100
60816	50
60820	74
60823	108
60824	75
60825	11
60821	75
60822	80
60829	80
60830	80
60831	75
60832	70
60833	69
60834	11
60826	75
60827	75
60828	40
60840	75
60841	104
60842	104
60843	80
60844	143
60845	11
55196	5
55197	19
55194	1
55195	1
60835	5
60836	120
60837	104
60838	104
60839	33
60852	71
60851	80
60853	5
60854	104
60855	104
60856	81
60857	156
60858	16
60846	100
60847	100
60848	100
60849	100
60850	51
60859	1
60860	100
60861	100
60862	100
60867	12
60863	104
60864	104
60865	106
60866	128
60872	75
60873	250
60874	111
60875	140
60876	10
55198	5
55199	8
55200	2
55201	7
60868	100
60869	100
60870	100
60871	30
55202	4
55203	3
55206	1
55204	8
55205	4
60878	24
60877	75
60879	104
60880	98
60881	104
60882	104
60883	104
60884	44
60885	183
60886	14
60888	100
60889	100
60890	100
60891	100
60887	12
60892	35
60893	75
60894	75
60895	55
60896	102
60897	184
60898	18
55207	6
60903	64
60904	104
60905	35
60906	154
60907	14
55208	11
60899	1
60900	100
60901	100
60902	100
60908	75
60909	40
60910	1
60911	57
55209	4
60914	93
60915	160
60916	10
60913	100
60912	100
60920	75
60921	56
60922	175
60923	15
60919	120
55210	6
55211	12
60917	100
60918	120
60924	100
60925	100
60928	2
60926	100
60927	100
60930	120
60931	53
60932	159
60933	17
60929	75

Please help me to get this working. Thanks in advance!

[Nine]

Instead of using multiple, hard to follow, ControlSend, just read and write the Excel cell directly with something like :

Local $sString = _Excel_RangeRead($oWorkbook, Default, "A1")
Local $iIndex = _ArraySearch($aArray, $sString)
_Excel_RangeWrite($oWorkbook, Default, $aArray[$iIndex][0],"C1")

Change your script to that approach, it would be easier to help you afterwards.

ps. your images do not show...

Edited February 12, 2020 by Nine

[Quintillius]

Thank you for the advice. The program where to write to isn't Excel though. It's Excel where the data is retrieved from.

I've attached a screenshot of my uploaded pics. Can you see the pics now?

[Nine]

I see.  I think the problem is you are copying too rapidly.  Put a sleep (400) after ControlSend($x, "", "", "{LEFT 2}").

Have you tested if you can read the controls in the other program, using Au3Info.exe ?

Edited February 12, 2020 by Nine

[Quintillius]

o.O

That fixed it. Thank you. 'Sleep(100)' does work too.

And yes I tested the controls with Au3Info.exe, but the only numbers that change are the coords unfortunately.