Help Please _ArraySearch

[clintnz05]

Hi,

I have two excel spreadsheets that have been read into two seperate arrays.

Now I want to search the array to find a value but I keep getting this error...

90) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:

For $x = 1 to $Baseline[0]

For $x = 1 to ^ ERROR

Please help - what am i doing wrong - I cant figure out hwo the _arraySearch works with the 2 arrays.

Code below...

;Open Baseline and read to array

$oExcel1 = _ExcelBookOpen(@ScriptDir & "\" & "DailyBaseline.xls",0) ;Open DailyBaseline.xls

_ExcelSheetActivate($oExcel1, "TAMU") ;Set TAMU as the active spreadsheet

$Baseline = _ExcelReadSheetToArray($oExcel1)

_ExcelBookClose($oExcel1) ; And finally we close out

;Open TempData and read to array

$oExcel2 = _ExcelBookOpen(@ScriptDir & "\" & "TempData.xls",0) ;Open TempData.xls

_ExcelSheetActivate($oExcel2, "TAMU") ;Set TAMU as the active spreadsheet

$TempData = _ExcelReadSheetToArray($oExcel2)

_ExcelBookClose($oExcel2) ; And finally we close out

For $x = 1 to $Baseline[0]

_ArraySearch($TempData, $Baseline[$x])

If @error then

Msgbox(0,"","not found")

Else

MsgBox(0,"","found")

EndIf

Next

At the moment I just want it to find the first record but I am struggling.

Any help I really appreciate it.

[clintnz05]

Also as an FYI.

The spreadsheets are 100 rows deep and 3 cols wide

[JoHanatCent]

Also as an FYI.

The spreadsheets are 100 rows deep and 3 cols wide

You'll need some more loops.

Do you need to compare all info per line for all three colums?

[clintnz05]

Hi thanks

What i want to do is

Go to 1st record in array 1

Get value from 1st row 1st col

Search second array for that value

Do some stuff and then move to next record

I just need help on how to do the search .

Is this error due to the amount of cols and I am not telling it what value to look at

[JoHanatCent]

I just need help on how to do the search .

Is this error due to the amount of cols and I am not telling it what value to look at

Almost ... The Help has the detail.

But you can try:

For $x = 1 To $Baseline[0][1]; Column number
    For $x2 = 2 To $TempData[0][0] - 2;Assuming you want to ignore header
        $found = _ArraySearch($Baseline, String($TempData[$x2][$x]),0, 0, 0, 1 ,1 , $x)
        If @error Then
            MsgBox(0, "", "not found")
        Else
            MsgBox(0, "Column no: " & $x & " found", "This is in row number: " & $found)
        EndIf
    Next
Next

Added the assuming bit!

Edited March 16, 2011 by JoHanatCent

[clintnz05]

I have to ask a really dumb question but when you declare the array - the numbers in the brackets represent what?

I dont understand how you have [0][0] for $Baseline and [0][1]-2 for $Tempdata.

I read the help and the forum and can't quite grasp that yet.

Both spreadsheets have 3 columns.

Col1 = Chars eg:john B

Col2 = Numbers eg: 17.52

Col3 = Chars eg: mb

What I want to do is look at Row1/Col1 in the Baseline spreadsheet and then locate that in Col1 of the TempData spreadsheet.

When I find it , compare Row1/Col2 in the Baseline spreadsheet with Col2 of the TempData spreadsheet where the match occured.

Do something with that value then

Move to the Row2/Col1 in the Baseline spreadsheet and repeat.

Any help please.

Its annoying the hell out of me.

Cheers

[michaelslamet]

How about this:

For $x = 1 to Ubound($Baseline) - 1

[JoHanatCent]

I have to ask a really dumb question but when you declare the array - the numbers in the brackets represent what?

I dont understand how you have [0][0] for $Baseline and [0][1]-2 for $Tempdata.

Any help please.

Its annoying the hell out of me.

Cheers

If you have more than one column then you get back a 2D Array.

[0][0] Shows how many row numbers are in the aRray

[0][1] Shown how many Columns are there in the aRray

You use these so that you don't have to count it manually.

Also changed the $baseline and $Tempdate arround because I did not read propperly the fist time round.

Try this. If it does not work you can post a sample of you're 2 x .xls here.(just change the .xls to .txt)

#Include <Excel.au3>
#Include <Array.au3>

;Open Baseline and read to array
$oExcel1 = _ExcelBookOpen((@ScriptDir & "\" & "DailyBaseline.xls",0) ;Open DailyBaseline.xls
;_ExcelSheetActivate($oExcel1, "TAMU") ;Set TAMU as the active spreadsheet
$Baseline = _ExcelReadSheetToArray($oExcel1)
_ExcelBookClose($oExcel1) ; And finally we close out


;Open TempData and read to array
$oExcel2 = _ExcelBookOpen((@ScriptDir & "\" & "TempData.xls",0) ;Open TempData.xls
;_ExcelSheetActivate($oExcel2, "TAMU") ;Set TAMU as the active spreadsheet
$TempData = _ExcelReadSheetToArray($oExcel2)
_ExcelBookClose($oExcel2) ; And finally we close out
#cs
From: OP

I dont understand how you have [0][0] for $Baseline and [0][1]-2 for $Tempdata.

#ce
_ArrayDisplay($Baseline," == > $baseline < == ")
_ArrayDisplay($TempData," == > $TempData < == ")

For $x = 1 To $TempData[0][1]; Column number
    For $x2 = 2 To $Baseline[0][0] - 2;Assuming you want to ignore the header row
        $found = _ArraySearch($TempData, String($Baseline[$x2][$x]),0, 0, 0, 1 ,1 , $x)
        If @error Then
            MsgBox(0, "", "not found")
        Else
            MsgBox(0, "Column no: " & $x & " found", "This is in row number: " & $found)
        EndIf
    Next
Next

[clintnz05]

Hi Spammer,

Thanks for all your help.

With that I was able to figure out where I was going wrong...

It is now:

$oExcel1 = _ExcelBookOpen(@ScriptDir & "\" & "DailyBaseline.xls",0) ;Open DailyBaseline.xls

_ExcelSheetActivate($oExcel1, "TAMU") ;Set TAMU as the active workbook

$Baseline = _ExcelReadSheetToArray($oExcel1)

_ExcelBookClose($oExcel1) ; And finally we close out

;Open TempData and read to array

$oExcel2 = _ExcelBookOpen(@ScriptDir & "\" & "TempData.xls",0) ;Open TempData.xls

_ExcelSheetActivate($oExcel2, "TAMU") ;Set TAMU as the active workbook

$TempData = _ExcelReadSheetToArray($oExcel2)

_ExcelBookClose($oExcel2) ; And finally we close out

For $i = 1 To $Baseline[0][0] ;$i is row number $Baseline

$found = _ArraySearch($TempData, $Baseline[$i][1], 0, 0, 0, 1) ;$found is row number in $TempData

If @error = 6 Then

MsgBox(0, "", "not found")

Else

MsgBox(0, "Row no: " & $found & " found", "This is in row number: " & $TempData[$found][1])

EndIf

Next

Thanks again for all your help and quick responces.

Over and out from NZ!

[JoHanatCent]

Hi Spammer,

Thanks for all your help.

With that I was able to figure out where I was going wrong...

Over and out from NZ!

NZ - so no Tsunami this time round?

Glad I could help but would prefer to go round known as Johan.

:Ment friendly ;}

Hi and dry RSA.

Edit: Added the friendly bit!

Edited March 17, 2011 by JoHanatCent