# Function Reference

# _Excel_RangeFind

Finds matching cells in a range or workbook and returns an array with information about the found cells

#include <Excel.au3>
_Excel_RangeFind ( $oWorkbook, $sSearch [, $vRange = Default [, $iLookIn = $xlValues [, $iLookAt = $xlPart [, $bMatchcase = False]]]] )

## Parameters

$oWorkbook | Workbook object
$sSearch | Search string. Can be a string (wildcards - *?~ - can be used) or any Excel data type. See Remarks
$vRange | [optional] A range object, an A1 range (string) or keyword Default to search all sheets of the workbook (default = keyword Default)
$iLookIn | [optional] Specifies where to search. Can be any of the XLFindLookIn enumeration (default = $xlValues)
$iLookAt | [optional] Specifies whether the search text must match as a whole or any part. Can be any of the XLLookAt enumeration (default = $xlPart)
$bMatchcase | [optional] True = case sensitive, False = case insensitive (default = False)

## Return Value

Success: | a two-dimensional zero based array with the following information: 0 - Name of the worksheet 1 - Name of the cell 2 - Address of the cell 3 - Value of the cell 4 - Formula of the cell 5 - Comment of the cell
Failure: | 0 and sets @error.
@error: | 1 - $oWorkbook is not an object or not a workbook object 2 - $sSearch is empty 3 - $vRange is invalid. @extended is set to the COM error code 4 - Error returned by the Find method. @extended is set to the COM error code

## Remarks

This function mimics the Ctrl+F functionality of Excel, except that it adds the cells comment to the result.
Excel recognizes the following wildcards:
? (question mark) - Any single character
* (asterisk) - Any number of characters
~ (tilde) followed by ?, *, or ~ - A question mark, asterisk, or tilde
When $vRange is specified as A1 range (string) then the active sheet of $oWorkbook is being searched.

## Related

_Excel_RangeReplace

## See Also

See http://office.microsoft.com/en-us/excel-help/wildcard-characters-HP005203612.aspx.

## Example

### Example 1

    #include "Extras\HelpFileInternals.au3"

    #include <Array.au3>
    #include <Excel.au3>
    #include <MsgBoxConstants.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, _Extras_PathFull("_Excel1.xls"))
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Find all occurrences of value "37000" (partial match)
    Local $aResult = _Excel_RangeFind($oWorkbook, "37000")
    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 '37000' (partial match)." & @CRLF & "Data successfully searched.")
    _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

### Example 2

    #include "Extras\HelpFileInternals.au3"

    #include <Array.au3>
    #include <Excel.au3>
    #include <MsgBoxConstants.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, _Extras_PathFull("_Excel1.xls"))
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Find all occurrences of string "=C10*10" in the formulas, exact match
    Local $aResult = _Excel_RangeFind($oWorkbook, "=C10*10", "A1:G15", $xlFormulas, $xlWhole)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 2", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 2", "Find all occurrences of string '=C10*10' in the formulas, exact match." & @CRLF & "Data successfully searched.")
    _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 2", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

### Example 3

    #include "Extras\HelpFileInternals.au3"

    #include <Array.au3>
    #include <Excel.au3>
    #include <MsgBoxConstants.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, _Extras_PathFull("_Excel1.xls"))
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Find all occurrences of string "test" in the comments
    Local $aResult = _Excel_RangeFind($oWorkbook, "test", Default, $xlComments)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 3", "Find all occurrences of string 'test' in the comments." & @CRLF & "Data successfully searched.")
    _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 3", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

### Example 4

    #include "Extras\HelpFileInternals.au3"

    #include <Array.au3>
    #include <Excel.au3>
    #include <MsgBoxConstants.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, _Extras_PathFull("_Excel1.xls"))
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & _Extras_PathFull("_Excel1.xls'.") & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Find all values with "Story" at the end using wildcards and exact match
    Local $aResult = _Excel_RangeFind($oWorkbook, "* Story", Default, Default, $xlWhole)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 4", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 4", "Find all values with 'Story' at the end using wildcards and exact match." & @CRLF & "Data successfully searched.")
    _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 4", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")