Check column for yesterday's date

**daillest319** · 07-31-2012, 02:55 PM

hi all,
im trying to write an if stament to check for yest date in coulmn AD. what i wrote so far it not working properly. any help would be great thanks!!!


Sub Macro1()


    Range("AD1:AD10000").Select
        
         If Date = Date - 1 Then
            MsgBox "date is correct"
            Else
            MsgBox "date is incorrect"
            End If
         

End Sub

**mike7952** · 07-31-2012, 03:05 PM

This will work.

Sub Macro1()
    For Each cell In Range("AD1:AD" & Cells(Rows.Count, "AD").End(xlUp).Row)
        If IsDate(cell.Value) Then
            If cell.Value = Date - 1 Then
               MsgBox "date is correct"
            Else
               MsgBox "date is incorrect"
            End If
        End If
    Next
End Sub

**daillest319** · 07-31-2012, 03:12 PM

works great but is it possbile to give me msg once and not for each cell it checks in that column. i just want to know if the date is in the column at all and if it is jsut send me a msg once whether it is or isn't

**GaidenFocus** · 07-31-2012, 03:13 PM

Try this:

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range
    FindString = date - 1
        With Sheets("Sheet1").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        End With

If rng is nothing then
msgbox "Date is incorrect."
else
msgbox "Date is correct."
end if

End Sub