How to Set _Excel_RangeFind search from Bottom to Top,

[Wellwellwell]

Hi.

My goal is to grab input from User, Then it will open an excel sheet to look into column H, do a _Excel_RangeFind to match the input from user and return the Row number.

Local $Excel_1 = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

$sWorkbook = @ScriptDir & "\Test.xlsx"
$oWorkbook = _Excel_BookOpen($Excel_1, $sWorkbook)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookOpen Example 1", "Error opening '" & $sWorkbook & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

;get last row
Local $LastRow = $oWorkbook.ActiveSheet.Range("H1").SpecialCells($xlCellTypeLastCell).Row
Local $sSearchrange = "H1:H" & $LastRow

;Search for input serial number
Local $rangeselect = $oWorkbook.Application.ActiveSheet.Columns("H").Select
Local $rRowaddress = _Excel_RangeFind($oWorkbook, $input, $sSearchrange, $xlValues, $xlWhole)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

Let say my excel file have below number:

Row 1 100

Row 2 23

Row 3 45

Row 4 69

Row 5 200

Row 6 40

Row 7 45

Row 8 66

If the input are "45", Currently my $rRowaddress always return Row3 because it search from Top to bottom. How to set _Excel_RangeFind search from Bottom to Top, i would like Excel_RangeFind return Row7 value

 

Thanks!

[Nine]

One way :

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Test.xls")

;get last row
Local $LastRow = $oWorkbook.ActiveSheet.Range("H1").SpecialCells($xlCellTypeLastCell).Row
Local $sSearchrange = "H1:H" & $LastRow

Local $aResult = _Excel_RangeRead($oWorkbook, Default, $sSearchrange)
Local $aList = _ArrayFindAll($aResult, 45)
MsgBox ($MB_SYSTEMMODAL, "Last found in", $aList[UBound($aList)-1]+1)

 

[water]

_Excel_RangeFind returns a 2D array holding all found cells (from top to bottom) meeting the search criteria.
Just process the returned array from bottom to top

[Wellwellwell]

Hi Nine and water,

Thanks for the suggestion. I have tried Nine codes and it works.

It make sense to get list of array then search from bottom to top.

 

Thanks!