Microsoft Office Tutorials and References
In Depth Information
Detecting Empty Cells
An infamous saying among computer programmers is “garbage in, garbage out.” What this
means is that if bad data is fed into a procedure, it will generate a bad answer. To guard
against this, the integrity of the data needs to be verified.
One element you should check before relying on the totals within the Y2001ByMonth.xls
workbook is that all values have been entered. You can do so by searching for empty cells and
prompting the user for a value to be entered into the cell. Searching for empty cells is accom
plished by using the Range object’s SpecialCells method. The syntax for the SpecialCells
expression .SpecialCells( Type, Value )
expression is a required range object.
Ty pe is a required value that should match one of the xlCellType constants, listed in Table 8-1.
Va flue is an optional variant. If Ty pe is xlCellTypeConstants or xlCellTypeFormulas , Va flue is
used to determine which type of cells to include in the result. The numeric values that corre
spond to the xlSpecialCellsValues constants, listed in Table 8-2, can be added together to
return more than one type of special cell.
Table 8-2. xlSpecialCellsValue Constants
Returns cells that contain errors
Returns cells that contain a logical (that is, Boolean)
Returns cells that contain numerical values
Returns cells that contain text
This FindEmptyCells procedure searches for empty cells in the data areas of Y2001ByMonth.xls
and prompts the user for a value to enter into each empty cell found.
Dim rgeSearch As Range, rgeEmpties As Range, rgeCurrent As Range
Dim strPrompt As String, strTitle As String, strReturn As String
Dim strRow As String, strAddress As String, strColumn As String
strTitle = "Microsoft Office Excel 2003 Programming Inside Out"
'Select proper range based on days in month
Set rgeSearch = Range("D6", Range("D6").End(xlDown))
Set rgeSearch = Range(rgeSearch, rgeSearch.End(xlToRight))
'Find Empties - End if none found
'Set Error trap for no cells found
On Error Resume Next