Microsoft Office Tutorials and References
In Depth Information
Set rgeEmpties = rgeSearch.SpecialCells(xlCellTypeBlanks)
If Err.Number = 1004 Then
strPrompt = "No empty cells found!"
MsgBox strPrompt, vbOKOnly + vbInformation, strTitle
Exit Sub
Else
strPrompt = "Unexpected error - " & Str$(Err.Number) & _
vbCrLf & Err.Description
MsgBox strPrompt, vbOKOnly + vbExclamation, strTitle
End If
'Reset error handler
On Error GoTo 0
'Loop through empties prompting for new values
For Each rgeCurrent In rgeEmpties
'Calculate time period
strAddress = "R5C" & Format(rgeCurrent.Column, "#0")
strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
strPrompt = "Value missing for " & Format(Range(strAddress), _
"h:mm AM/PM")
'Calculate day
strAddress = "R" & Format(rgeCurrent.Row, "#0") & "C2"
strAddress = Application.ConvertFormula(strAddress, xlR1C1, xlA1)
strPrompt = strPrompt & " on " & ActiveSheet.Name & " " & _
Range(strAddress)
strReturn = InputBox(strPrompt, strTitle)
rgeCurrent = CSng(strReturn)
Next rgeCurrent
End Sub
Using Named Ranges
You might already know that you can select a range of cells on a worksheet and give it a name
by typing it in the Name Box. This lets you quickly select the range by choosing its name from
the drop-down list provided in the Name Box. These same names are available to you within
a macro. Instead of providing the cell coordinates for a range, you can use the name of the
range instead.
Defining a Named Range
Excel stores the names of defined ranges within the Names collection, which is a property of
the Workbook object. Using the Add method, you can create a Named range within the
workbook by specifying the name you want to use and the range it should point to.
If you wanted to do some analysis of the sales data for each hourly time period within the
Y2001ByQuarter.xls workbook, you could make your formulas easier to read by defining
each time period as a range. Rather than manually selecting the range and typing a name, you
Search JabSto ::




Custom Search