Jump to content

How to Set _Excel_RangeFind search from Bottom to Top,


Recommended Posts

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!

Link to post
Share on other sites

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)

 

Link to post
Share on other sites

_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 :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...