Find all dates between two dates and then find if there is missing data

[patrickmt]

What I want to do may not be simple but I was hoping someone can help me out.

I have a "Print & Save" macro on a spreadsheet. When the user pushes the button it also checks to make sure they have completed the "inspection" section of one of the work book. That was easy to do because it is the same area each time that I have named.

Sub PrintSaveReport()
Dim rng As Range
    Set rng = [Monthly_CI]
Dim rng2 As Range
    Set rng2 = [MonthlyYes]
Dim rng3 As Range
    Set rng3 = [MonthlyComment]

If Application.WorksheetFunction.CountA(rng) = 0 Then _
Response = MsgBox("You have not completed the Monthly Cyclone Inspection." & Chr(13) & "Please go back and complete the inspection before printing", vbExclamation + vbOKOnly, "Please Read!")
If Response = vbOK Then Exit Sub

If Application.WorksheetFunction.CountA(rng2) And Application.WorksheetFunction.CountA(rng3) = 0 Then _
Response = MsgBox("You have indicated a 'Yes' on the Monthly Cyclone Inspection but have not included details containing corrective actions." & Chr(13) & "Please go back and complete the inspection before printing", vbExclamation + vbOKOnly, "Please Read!")
If Response = vbOK Then Exit Sub

'---Clear Monthly Cyclone Inspection
Sheets("CTS_PinChipHoursOperated").Range("C42:C44").ClearContents
Sheets("CTS_PinChipHoursOperated").Range("E42:E44").ClearContents
End Sub

I have now been asked to see if I can achieve the same thing on the data entry page.

Column A - Dates (there will be no missed days)
Column B - has other data below but it also houses the "Period Start and End" dates. I have another sheets looking at this piece of data to do an index-match to fill in the reports.
Column H through M - Houses the required information. Now, if one is left blank that is okay, what I need to do is if all 5 are blank for the print macro to stop and prompt the user to go back and fill in the missing data.

So, I guess I need to do some type of find by row and search for all cells for the particular day in the date range and 5 one day which has all 5 cell blank....

Here is the rest of my big mess of code...maybe someone can even clean it up for me

Sub PrintSaveReport()
Dim strFilename1     As String
Dim strFilename2     As String
Dim strFilename3     As String
Dim rngRange        As Range
Dim Response As VbMsgBoxResult
Dim rng As Range
    Set rng = [Monthly_CI]
Dim rng2 As Range
    Set rng2 = [MonthlyYes]
Dim rng3 As Range
    Set rng3 = [MonthlyComment]

Dim wb As Workbook
Dim wsOrder As Worksheet
Dim wsCTS As Worksheet
Dim wsPDL As Worksheet
Dim wsPCDPC As Worksheet

Set wb = ThisWorkbook
Set wsOrder = wb.Sheets("RAWData")
Set wsCTS = wb.Sheets("CTS_PinChipHoursOperated")
Set wsPDL = wb.Sheets("PressureDropLog")
Set wsPCDPC = wb.Sheets("PinChipDPChecks")

'---confirm this action---
Response = MsgBox("This action will print and save a PDF of the data selected in the date range?" & Chr(13) & "Are you sure you want to continue?", vbExclamation + vbYesNo, "Please Read!")
If Response = vbNo Then Exit Sub

'---Check for blanks---
If Application.WorksheetFunction.CountA(rng) = 0 Then _
Response = MsgBox("You have not completed the Monthly Cyclone Inspection." & Chr(13) & "Please go back and complete the inspection before printing", vbExclamation + vbOKOnly, "Please Read!")
If Response = vbOK Then Exit Sub

If Application.WorksheetFunction.CountA(rng2) And Application.WorksheetFunction.CountA(rng3) = 0 Then _
Response = MsgBox("You have indicated a 'Yes' on the Monthly Cyclone Inspection but have not included details containing corrective actions." & Chr(13) & "Please go back and complete the inspection before printing", vbExclamation + vbOKOnly, "Please Read!")
If Response = vbOK Then Exit Sub

'---Used to get the date which is used in the file name when saved ---
dtmDate = wsOrder.Range("ReportDateS").Value

'---Print pages based upon preset PrintArea---
wsCTS.PrintOut
wsPDL.PrintOut
wsPCDPC.PrintOut

'---save selcted range---
'---Create File name with dateStamp as well as the range selected above---
strFilename1 = Format(dtmDate, "yyyy") & " CTS_PinChipHoursOperated " & Format(dtmDate, "mmmyy")

Sheets(Array("CTS_PinChipHoursOperated")).Select
Sheets("CTS_PinChipHoursOperated").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "G:\Environmental Logs\Pin Chip DP Logs\CTS_PinChipHoursOperated_export\" & strFilename1 & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True
 
'--Save2
 
strFilename2 = Format(dtmDate, "yyyy") & " PressureDropLog " & Format(dtmDate, "mmmyy")

Sheets(Array("PressureDropLog")).Select
Sheets("PressureDropLog").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "G:\Environmental Logs\Pin Chip DP Logs\PressureDropLog_export\" & strFilename2 & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True
'--Save3

strFilename3 = Format(dtmDate, "yyyy") & " PinChipDPChecks " & Format(dtmDate, "mmmyy")

Sheets(Array("PinChipDPChecks")).Select
Sheets("PinChipDPChecks").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "G:\Environmental Logs\Pin Chip DP Logs\PinChipDPChecks_export\" & strFilename3 & ".pdf" _
    , Quality:=xlQualityStandard, IncludeDocProperties:=True
    
'---Clear Monthly Cyclone Inspection
'Sheets("CTS_PinChipHoursOperated").Unprotect
Sheets("CTS_PinChipHoursOperated").Range("C42:C44").ClearContents
Sheets("CTS_PinChipHoursOperated").Range("E42:E44").ClearContents
'Sheets("CTS_PinChipHoursOperated").Protect
End Sub

[patrickmt]

To make my question simpler - I am looking for a way, using VBA in excel, to look up a piece of data in one row (in my case a range of dates starting in row A7) and then check to see if rows H:M are all blank.

All my dates will always be in order (12/1, 12/2, 12/3...,12/31)

The start date is kept in B2 and the end date in B3.

I found a piece of code which can find the dates but then searching for the blanks in between that date range AND reporting which dates are missing data... well. Help?

'---check to make sure all data has been entered---
LookupColumn = "A" '---Define the LookupColum---
StartDate_Value = wsOrder.Range("B2").Value '---Use whatever you need to define the input values---
EndDate_Value = wsOrder.Range("B3").Value '---Use whatever you need to define the input values---

For i = 1 To 30000
    If wsOrder.Range(LookupColumn & i).Value = EndDate_Value Then EndDate_Row = i
Next i

For j = EndDate_Row To 1 Step -1
    If wsOrder.Range(LookupColumn & j).Value = StartDate_Value Then StartDate_Row = j
Next j

Dim MyDateRange As Range: Set MyDateRange = wsOrder.Range(LookupColumn & StartDate_Row & ":" & LookupColumn & EndDate_Row)
MsgBox "All Data has been entered for Period = " & StartDate_Value & " - " & EndDate_Value

[patrickmt]

Bump, anyone?

[matthew_b]

I'm not sure I get what you're asking. Is it that if start date is January 1, 2015 and end date is January 31, 2015 that all dates between these two are in order in the cells between? (A1 = Jan 1, A2 = Jan 2, etc.)? If so, maybe something like this:

For k = StartDate_Row to EndDate_Row - 1
     If wsOrder.Range(LookupColumn & k+1) - wsOrder.Range(LookupColumn & k) <> 1 Then
           <WHATEVER CODE YOU WANT TO RUN WHEN A DATE IS MISSING>
           Exit For
     End if
Next k

This code will check to ensure that each cell is only one day apart from the next cell down.

[patrickmt]

Thanks for your reply but no, that is not what I am asking.

I want a VBA code that looks at data in a separate column to check to see if it's empty, blank, zero, etc. but only if it falls between two dates.

If C2 = 0 (which is in the same row as 12/15/14) & Is between the dates 12/1/14 & 12/31/14 (which it is because it is in the same row as 12/15/14) then stop and notify operator that on 12/15/15 there was nothing entered.

On the flip side:

If C2 = 0 (which is in the same row as 8/7/13) & Is between the dates 12/1/14 & 12/31/14 (which it is not because it is in the same row as 8/7/13) then continue code to next thing.

This topic is now also located on Ozgrid http://www.ozgrid.com/forum/showthread.php?t=192901 to see if I can get anymore help, which so far I haven't.