Sign in to follow this  
Followers 0
clintnz05

Help Please _ArraySearch

10 posts in this topic

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.

Share this post


Link to post
Share on other sites



Also as an FYI.

The spreadsheets are 100 rows deep and 3 cols wide

Share this post


Link to post
Share on other sites

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?


Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by JoHanatCent

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

How about this:

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by JoHanatCent

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0