Test if Cell is Empty or Doesn't Exist

[scott]

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

 

[K Dales]

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

 

[sebastienm]

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