VBA check whether a row for the current date has been filled in and return yes/no value in another cell.

[drefiek2]

Hi,
I have 12 sheets, each with a table with dates on, each row is for one date during that month (i.e. January sheet has 31 rows and column A is the date column), and I would like some VBA code which will check that the row for the current date has been filled in (the user either types a 0 or 1 in the cells), in its entirety (there are 6 columns). And returns either the word 'yes' or 'no' to a cell on a different sheet.
I am not sure if the code needs to use the system date? Either way I am not sure what code to use to do this so any help is greatly appreciated.

 

[mumps]

in its entirety (there are 6 columns)

Does that include column A?

returns either the word 'yes' or 'no' to a cell on a different sheet.

What is the name of the sheet and destination cell address?

 

[drefiek2]

Does that include column A?

What is the name of the sheet and destination cell address?

Hi there, no it does not include column A as that column is already filled in as dates, so will be columns B:G
Sheet name is 'verification' and destination cell address is O4.
Sorry for lack of info in original post, was end of 12 hour night shift!!

 

[mumps]

This macro assumes that the sheets are named using the full month names, for example: January, February, March, etc.

Sub CheckCurrentDate()
    Application.ScreenUpdating = False
    Dim foundDate As Range
    Set foundDate = Sheets(MonthName(Month(Date))).Range("A:A").Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
    If WorksheetFunction.CountA(Range("B" & foundDate.Row).Resize(, 6)) = 6 Then
        Sheets("verification").Range("O4") = "yes"
    Else
        MsgBox ("The data for today's date is not complete in the " & MonthName(Month(Date)) & " sheet.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[drefiek2]

This macro assumes that the sheets are named using the full month names, for example: January, February, March, etc.

Sub CheckCurrentDate()
    Application.ScreenUpdating = False
    Dim foundDate As Range
    Set foundDate = Sheets(MonthName(Month(Date))).Range("A:A").Find(Date, LookIn:=xlFormulas, lookat:=xlWhole)
    If WorksheetFunction.CountA(Range("B" & foundDate.Row).Resize(, 6)) = 6 Then
        Sheets("verification").Range("O4") = "yes"
    Else
        MsgBox ("The data for today's date is not complete in the " & MonthName(Month(Date)) & " sheet.")
    End If
    Application.ScreenUpdating = True
End Sub

Thank you, it works!

 

[mumps]

You are very welcome.