creativewonders Posted September 26, 2022 Share Posted September 26, 2022 Hi, I want to search for two Excel Cell Values ( Range A2:D25 ) - but I don' t know how ? 😞 Example: Values To Find : 'C14416821'  in cell A2: A25  and 'UC-R01' in cell B2:B25 if both values match the result should be the address of the row. I hope it was not so complicated and you know what I mean 🙂 Thanks in advance.  test.xlsm Link to comment Share on other sites More sharing options...
Luke94 Posted September 26, 2022 Share Posted September 26, 2022 Check out _Excel_RangeFind. Link to comment Share on other sites More sharing options...
creativewonders Posted September 26, 2022 Author Share Posted September 26, 2022 I try everything but no result 😞 Link to comment Share on other sites More sharing options...
Danp2 Posted September 26, 2022 Share Posted September 26, 2022 Post code showing what you've tried. Latest Webdriver UDF Release Webdriver Wiki FAQs Link to comment Share on other sites More sharing options...
creativewonders Posted September 27, 2022 Author Share Posted September 27, 2022 16 hours ago, Danp2 said: Post code showing what you've tried. Sorry, but the whole code of my program is too long to show here... Here I extracted one example, but with this method I always found one value, but I need to find both and if it' s match the row number ...  expandcollapse popup#include <MsgBoxConstants.au3> #include <FileConstants.au3> #include <Array.au3> #include <Excel.au3> Local $sValueToFind = "UC-R01/2" ; "C14416821" Local $sFileOpenDialog = @ScriptDir & "\sources\test.xlsm" If @error Then MsgBox($MB_SYSTEMMODAL, "", "No file(s) were selected.") ConsoleWrite($sFileOpenDialog & @CRLF) ShellExecute($sFileOpenDialog, "", "", "open", @SW_MAXIMIZE) If @error Then Exit MsgBox(0, "ERROR", "Error in ShellExecute") Local $aExcelFileName = StringRegExp($sFileOpenDialog, '[^\\]*$', 1) If Not IsArray($aExcelFileName) Then Exit MsgBox(0, "ERROR", "Error in StringRegExp" & @CRLF & "@error: " & @error) If Not WinWait($aExcelFileName[0], "", 15) Then Exit MsgBox(0, "ERROR", "Error in WinWait") Sleep(3000) Local $oWorkbook = ObjGet($sFileOpenDialog) If @error Or Not IsObj($oWorkbook) Then Exit MsgBox($MB_SYSTEMMODAL, "ERROR", "Error in ObjGet") Local $aResult = __Excel_RangeFind($oWorkbook, $sValueToFind) If Not @error And IsArray($aResult) Then _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example", "", 0, "|", "Sheet|Cell|Value|Formula") Else Exit MsgBox(0, "Error", "The value/text (" & $sValueToFind & ") could not be found.") EndIf Func __Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default) If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0) If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0) If $iLookIn = Default Then $iLookIn = $xlValues If $iLookAt = Default Then $iLookAt = $xlPart If $bMatchcase = Default Then $bMatchcase = False Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet If $vRange = Default Then $bSearchWorkbook = True $oSheet = $oWorkbook.Sheets(1) $vRange = $oSheet.UsedRange ElseIf IsString($vRange) Then $vRange = $oWorkbook.Activesheet.Range($vRange) If @error Then Return SetError(3, @error, 0) EndIf Local $aResult[100][4], $iIndex = 0, $iIndexSheets = 1, $iNumberOfSheets = $oWorkbook.Worksheets.Count While 1 $oMatch = $vRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase) If @error Then Return SetError(4, @error, 0) If IsObj($oMatch) Then $sFirst = $oMatch.Address While 1 $aResult[$iIndex][0] = $oMatch.Worksheet.Name $aResult[$iIndex][1] = $oMatch.Address $aResult[$iIndex][2] = $oMatch.Value $aResult[$iIndex][3] = $oMatch.Formula $iIndex = $iIndex + 1 If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][4] $oMatch = $vRange.Findnext($oMatch) If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop WEnd EndIf If Not $bSearchWorkbook Then ExitLoop $iIndexSheets = $iIndexSheets + 1 If $iIndexSheets > $iNumberOfSheets Then ExitLoop $sFirst = "" $oSheet = $oWorkbook.Sheets($iIndexSheets) If @error Then ExitLoop $vRange = $oSheet.UsedRange WEnd ReDim $aResult[$iIndex][4] Return $aResult EndFunc Exit  Link to comment Share on other sites More sharing options...
Danp2 Posted September 27, 2022 Share Posted September 27, 2022 It seems like you are using an older or modified version of the Excel UDF by @water. Why not use the current one that comes with AutoIt? Have you tried taking the results from _Excel_RangeFind and using them to check the value of adjacent cells? Â Latest Webdriver UDF Release Webdriver Wiki FAQs Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now