Styjun

I am currently working on a sheet, specifically column that contains various data types; where I am interested in calculating difference between the first date occured in the column and the next one to it (because the main problem is that the data in the column is heterogeneous and I am interested just in succession ).

So for each cell in the range I have to check whether is a Date , and if so calculate difference between the actual date and the that of the next cell.

I have tried some code but all I can say it is not stable since the IsDate function is acting wierd and seems to change the outcome for different reasons but never the same.

Sub loopDate()
 Dim rnge, cell As range
 Set rnge = range("P1:P21")

 Application.ScreenUpdating = True

 For Each cell In rnge 
 cd = cell.Value2
 If IsDate(cd) = True Then 
 If IsDate(ActiveCell.Offset(1, 0)) = True Then
 n = DateDiff("d", cd, ActiveCell.Offset(1, 0))
 If n < 0 Then
 MsgBox "here is a difference " & n
 Else
 MsgBox "normal pos diff " & n
 End If
 Else
 MsgBox "contenent of the this cell isnt date intIF "
 End If 
 Else
 MsgBox "contenent of the this cell isnt date outIF " 
 End If
 Next 
End Sub

I am expecting either a negative or positive number that will refer to difference between two dates and tell weather we still gave time or we already got the deadline passed.

thanks for any help or suggestions about the code above.

try this:

Sub loopDate()
 Dim rnge, cell As Range
 Set rnge = Range("P1:P21")

 For Each cell In rnge
 If IsDate(cell) = True Then
 If IsDate(cell.Offset(1, 0)) = True Then
 n = DateDiff("d", cell, cell.Offset(1, 0))
 If n < 0 Then
 MsgBox "here is a difference " & n
 Else
 MsgBox "normal pos diff " & n
 End If
 Else
 MsgBox "contenent of the this cell isnt date intIF "
 End If
 Else
 MsgBox "contenent of the this cell isnt date outIF "
 End If
 Next

End Sub

If I understand you correctly, this is what you're trying to achieve; I have cleaned up some of the unnecessary bits and now you just have to edit DateCol and FindLastRow as is necessary.

Sub loopDate()

'Dim rnge As Range, cell As Range
Dim DateCol As Integer, FindLastRow As Single Dim i As Single

'Set rnge = Range("P1:P21")

'Application.ScreenUpdating = True


' For Each cell In rnge
DateCol = 16
FindLastRow = 21
For i = 1 To FindLastRow

' cd = cell.Value2



 If IsDate(Cells(i, DateCol)) Then

 If IsDate(Cells(i, DateCol + 1)) Then



 n = DateDiff("d", Cells(i, DateCol).Value, Cells(i, DateCol + 1).Value)


 If n < 0 Then

 MsgBox "here is a difference " & n
 Else
 MsgBox "normal pos diff " & n


 End If

 Else
 MsgBox "contenent of the this cell isnt date intIF "
 End If

 Else
 MsgBox "contenent of the this cell isnt date outIF "

 End If

 ' Next
 Next i


 End Sub