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]]]] )
| $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) |
| 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 |
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.
See http://office.microsoft.com/en-us/excel-help/wildcard-characters-HP005203612.aspx.
#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, @ScriptDir & "\Extras\_Excel1.xls")
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 "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")
#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, @ScriptDir & "\Extras\_Excel1.xls")
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 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")
#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, @ScriptDir & "\Extras\_Excel1.xls")
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 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")
#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, @ScriptDir & "\Extras\_Excel1.xls")
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 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")