SearchForReferencesToBlankCells

Searches a specified area of a worksheet for cells that refer to other cells that are blank, selecting any that are found in a multi-area range.

A spreadsheet user might feel that if a cell is blank, he is free to type something into it.  A formula that refers to that blank cell could then start giving an unintended result.  In some cases, a reference to a blank cell is simply the result of mistyping the address of a cell that is not blank.

Applies to

IOAKAddIn, IOAKAPI

SYNTAX

result1 = expression1.SearchForReferencesToBlankCells

Set result2 = expression2.SearchForReferencesToBlankCells(Range1)

expression1   Required.  An expression that returns an IOAKAddIn object.

expression2   Required.  An expression that returns an IOAKAPI object.

result1   An OAKResult enumeration indicating the success or otherwise of the action.

result2   A Range containing of all the cells found within Range1 that refer to other cells that are blank, or Nothing if no such cells are found.

Range1           Required Range.  The range in which to search for cells containing a formula that refers to other cells that are blank.

Remarks

When applied to an IOAKAddIn object, the SearchForReferencesToBlankCells method performs the same action as the OAK | Search selection | Reference to Blank Cells command.   It searches for cells within the current selection that refer to blank cells.

When applied to an IOAKAPI object, the SearchForReferencesToBlankCells method applies searches for cells within the the range indicated by the parameter submitted to the method that refer to blank cells.

Wrinkles

When applied to an IOAKAddIn object, the SearchForReferencesToBlankCells method follows the One-cell rule.  When applied to an IOAKAPI object, it does not: if supplied a Range parameter describing a single cell, it will look in that cell only.

When applied to an IOAKAddIn object, the method will display a dialog box if no references to blank cells are detected, just as the equivalent OAK user-interface command does.

Neither method will detect a reference to a blank cell that is located on a different worksheet from the formula.