Excel search,compare?

[AoRaToS]

I have two excel files, one contains stuff like:

A B C

BOX1 name 7643294

BOX2 name 4324523

BOX4 name 5553254

Then I have a second one that has only numbers, all of them are in the first one but the order is random, so what I want is:

Read a number out of the second file, find the corresponding one in the first file and take it's box number to put it in a cell next to the number in the second file.

It would be like

$sFilePath1 = @ScriptDir & "\file1.xls" ;This file should already exist
$sFilePath2 = @ScriptDir & "\file2.xls" ;This file should already exist

$oExcel2 = _ExcelBookOpen($sFilePath2)
$oExcel1 = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

Sleep(4000)

For $count = 1 to 250
    $value = _ExcelReadCell($oExcel2, $count,6)
        ; $searchcell = Search($oExcel1, $value)  should return something like F35 etc
        ; $box = _ExcelReadCell($oExcel1, "*",6)    *split $searchcell and use only the number to make A35 (I can do this)
    _ExcelWriteCell($oExcel2, $value, $count,7)
Next

Is there some way of searching an excel file using a UDF?sending Ctrl+F would take long and require allot of checking if the window has appeared, sleep to let the search finish, get the selected item...

[Melba23]

AoRaToS,

Surely the way to do this would be to use a loop and _ExcelReadCell? Something like this:

For $count2 = 1 to 250
    $value2 = _ExcelReadCell($oExcel2, $count,6)
    For $count1 = Whatever values you need to search
        $value1 = _ExcelReadCell($oExcel1, some function of $count1)
        If $value1 = $value2 Then ExitLoop
    Next
; $count1 will hold the index of the found number
; but you will need to errorcheck to see if you fell out of the loop without finding the number!
; now read another cell in that row to get the box number
    $box = _ExcelReadCell($oExcel1, some other function of $count1)
; split $searchcell and use only the number to make A35   (Quote: "I can do this")
    _ExcelWriteCell($oExcel2, $value, $count2,7)
Next

I do not have Excel so I cannot test this, but the logic should be good. Think of it as "an exercise for the student"!

M23