Obtaining the Cell a specific value is in. (Excel)

[Kruxe]

Hello Everyone,

I was wondering if anyone could offer some help on this. What I am trying to do is search a for a value in an excel range using _Excel_RangeFind which works great but I need to be able to extract what cells the values are found in is there any way to do this? I can see the cells when I use _ArrayDisplay to display the Range but is there a way to actually extra the cell locations?

thanks in advanced for the aid!

Kruxe 

[water]

_Excel_RangeFind returns an array with the information you are looking for. The help file explains the content of this array.
Can you please post the code you are running?

[Kruxe]

15 hours ago, water said:

_Excel_RangeFind returns an array with the information you are looking for. The help file explains the content of this array.
Can you please post the code you are running?

Hi Water,

 

Basically i am using one of the examples in the help file and it works great and displays the array showing the cells each occurrence of X is in but how can i extract the cell locations each occurrence is in? 

 

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

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel,"C:\Excel list.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf

; Find all occurrences of value "X" (partial match)
Local $aResult = _Excel_RangeFind($oWorkbook, "X","N11:N50")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'X' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

 

[water]

$aResult[$i][2] holds the address of the finding. $i is the row number in the array.

[Kruxe]

10 minutes ago, water said:

$aResult[$i][2] holds the address of the finding. $i is the row number in the array.

sorry water, 

I am still a giant noob, would it be possible to get some example code?

thanks!

[water]

When you run the first example found in the help file for _Excel_RangeFind you get a list with many findings. If you want to get the address of row 5 you simply have to access $aResult[5][2].

Quote

I need to be able to extract what cells the values are found in ...

So it depends what you want to do with the extracted information.

[Kruxe]

3 hours ago, water said:

When you run the first example found in the help file for _Excel_RangeFind you get a list with many findings. If you want to get the address of row 5 you simply have to access $aResult[5][2].

So it depends what you want to do with the extracted information.

Water,

This information puts me on the right track, thanks again for the help!

Kruxe