Search in Excel

[creativewonders]

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

[Luke94]

Check out _Excel_RangeFind.

[creativewonders]

I try everything but no result 😞

[Danp2]

Post code showing what you've tried.

[creativewonders]

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

 

[Danp2]

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?