Extract multiple rows from Excel Read returned array

[31290]

Good day everyone, 

So, I'm trying to extract all rows from a searched string within an array. Pretty sure I'm missing something here but arrays are not my thing despite I've read the help file, the wiki and many topics on the forum (yeah yeah, I'm probably dumb :D)

Using the Excel UDF, I can easily put the whole file content into an array:

#include <Excel.au3>
#include <Array.au3>
#include <MsgBoxConstants.au3>

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

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

; Read column C
Global $oRange = $oWorkbook.ActiveSheet.UsedRange.SpecialCells($xlCellTypeLastCell)
Global $aResult = _Excel_RangeRead($oWorkbook, Default, "B1:B"" & $oRange.Row)
_ArrayDisplay($aResult)

_Excel_BookClose($sWorkbook)
_Excel_Close($oExcel)

From above, I can just have the whole B$ Rows from the whole array content but what I'd like to achieve is the following:

Search for something in Col A$ and then retrieve the corresponding rows from the results:

$LAP = InputBox("LAP", "Search for LAP", "")

Doing so, I 'm able to retrieve the result index in the array by displaying $LAP content but can't get the related rows.

Here's the excel file sample

100% this is simple as f* but my mind seems not to want to find it more

Thanks a lot any help that might come.

-31290-

Edited September 29, 2021 by 31290

[Subz]

Maybe something like:

_SearchArray()
Func _SearchArray()
    Local $sInputBox
    While 1
        $sInputBox = InputBox("LAP", "Search for LAP", "")
        If @error Then Return SetError(1, 0, "")
        Exitloop
    Wend
    ;~ Search in Column A for Input and return row index
    Local $iSearchResult = _ArraySearch($aResult, $sInputBox, 0, 0, 0, 0, 1, 0)
    If Not @error Then
        ;~ Display Second Column Item
        MsgBox(4096, "", $aResult[$iSearchResult][1])
    EndIf
EndFunc

 

Edited October 2, 2021 by Subz

[31290]

@Subz I warmly thank you for pointing me in the way I want! 

Fortunately, there are only 6 Rows I need to display  

I admit I forgot to get a separate function for me to make the search but I was really unable to make the code inside it.

Have a good one!

 

[JockoDundee]

@Subz, this part seems a bit dodgy, no?

If @error Then Return _SearchArray()

Maybe you can only hit cancel 4000 times or so?

[Subz]

@JockoDundee  Yeah I should have tested first, have updated the code.

[JockoDundee]

@Subz, I guess what I’m referring to is how you are calling _SearchArray() from inside _SearchArray() itself:

If StringStripWS($sInputBox, 8) = "" Then Return _SearchArray()

Which could make the function call stack crash eventually, because the recursion limit might be reached.

[Subz]

Didn't think of that, assumed no one would reach the limit, however with that being said, probably just putting the InputBox inside a loop should perform the same task.

[JockoDundee]

54 minutes ago, Subz said:

probably just putting the InputBox inside a loop should perform the same task.

Yeah, no worries.

54 minutes ago, Subz said:

assumed no one would reach the limit

As long as the user didn’t accumulate 4000 cancels before the user closes the program, no one would ever know