Test if Cell is Empty or Doesn't Exist

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

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
>
>
>

Hi,
Check if a cell was found by comparing to Nothing. And check if it is a date
with IsDate( ). Something like:

Sub GetDate()

Dim cell As Range, cellOffset As Range
Dim sDateRange As Date
Dim sDateString as string '<--- date string
Set cell = FindCell("To Period", Sheets(1).Cells).Offset(0, 2)

if cell is nothing then 'case no cell found, set date to zero
sDateRange=0
else
sDateString = Right(cell.Value, 2) & "/" & (Left(cell.Value,
Len(cell) - 2))
' if is a date then set date to date, else to zero
sDateRange= iif(isdate(sDateString),CDate(sDateString),0)
End if

End Sub

Regards,
Sebastien
"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
>
>
>