Help with some code please

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

I am trying to use the code below to check dates in column C, D and E
against a date range in columns I and J and return "Yes" or "No" in column
N.

If C, D and E all contain dates it works fine, but there are some blank
cells and cells containing the word "Error".

Is there some way of checking whether there is a date in the cell before
getting the result?

For the cells containing "Error" I would like "Error" displayed in column N.

Thanks in advance.

Gareth

Sub CheckDateRange()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In rng
If cell.Value > cell.Offset(0, 7).Value Or cell.Offset(0, 1).Value <
cell.Offset(0, 6).Value Or _
cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then
cell.Offset(0, 11).Value = "Yes"
Else
cell.Offset(0, 11).Value = "No"
End If
Next cell
End With
Application.ScreenUpdating = True
End Sub
 
See this:

Sub CheckDateRange()
Application.ScreenUpdating = False
With Worksheets("Sheet1")
Set rng = .Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)
For Each cell In rng
If IsError(cell.Value) Or Not IsDate(cell.Value) Then
cell.Offset(0, 11).Value = IIf(IsError(cell.Value), "Error", "")
Else
If cell.Value > cell.Offset(0, 7).Value Or cell.Offset(0,
1).Value < cell.Offset(0, 6).Value Or _
cell.Offset(0, 2).Value < cell.Offset(0, 6).Value Then
cell.Offset(0, 11).Value = "Yes"
Else
cell.Offset(0, 11).Value = "No"
End If
End If
Next cell
End With
Application.ScreenUpdating = True
End Sub
 
Back
Top