Set cell = FindCell("To Period", Sheets(1).Cells).Offset(0, 2)
' This line will not only make sure it is not blank, but verify that the
cell entry meets
' the need to be converted to a date - you can modify it to test for any
pattern:
If Cell.Value like "##*##" Then
sDateRange = CDate(Right(cell.Value, 2) & "/" & (Left(cell.Value,
Len(cell) - 2)))
Else
MsgBox "Invalid cell contents",vbExclamation,"ERROR:"
End If


"scott" wrote:

Below I have a function that finds a string within a range in FindCell()
function. My sub GetDate() finds a cell containing a date just fine, except
if the cell is empty or doesn't exist.

How can I add a test to the "FindCell("To Period",
Sheets(1).Cells).Offset(0, 2)" part to display a simple msgbox if the cell
is empty or doesn't exist?


Sub GetDate()

Dim cell As Range, cellOffset As Range
Dim sDateRange As Date

Set cell = FindCell("To Period", Sheets(1).Cells).Offset(0, 2)

sDateRange = CDate(Right(cell.Value, 2) & "/" & (Left(cell.Value,
Len(cell) - 2)))

End Sub

Function FindCell(searchFor As String, _
searchRange As Range) As Range

With searchRange

Set FindCell = .Find(what:=searchFor, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

End With

End Function