Countif in VBA msgbox using a variable date as the criteria

[DavidT77]

Hi...I have a macro that copies data from a weekly report into an ongoing tracker that is able to track time-series trends based on these weekly reports. I use a variable input box creatively named "RptDate" to input the date as mm/dd/yyyy which the macro then uses to fill in column I of my tracker so that I can filter by the report date to see the trends. I then have a message box at the end of my macro that says "Success" just to let me know it worked and has concluded. I would now like to add a countif statement into this message box to let me know how many rows of data were entered based on the RptDate. Here is what I have so far...

Sub COT_Data_ETL()​

Dim RptDate As Date​

Dim Institutions As Long​

​

RptDate = InputBox("Enter the date of the COT report in the format of MM/DD/YYYY.", "Report Date")​

Institutions = WorksheetFunction.CountIf(Columns("I"), RptDate)

..((ETL coding here...)

​

MsgBox Institutions & " Success!!! The pivots and charts have all been refreshed and this workbook has been saved. If you're ready, " & _​

"copypaste the data you need into the Tracker from the ""Pivot_for_Tracker"" tab. Be sure to select the correct date in the slicer before copypasting. " & _​

"Otherwise, you are done for now.", vbOKOnly, "COT Data ETL"​

The result of the Institutions variable in the MsgBox at the end is "0" where it should show the actual number of rows copied over based on the RptDate criteria. I'm expecting to see "50" but if i see a different number, then that tells me that there were locations missing from the ETL and that I'll need to investigate further. However, the Institutions variable doesn't seem to like using the RptDate variable in the formula. But, if I hard-code it with "4/12/2019", the most recent report date, then it works fine. How can I get the CountIf formula in my Institutions variable to reference the RptDate variable?

Hope this makes sense and Thanks.

 

[dmt32]

Hi,
You need to be mindful what comes out of a textbox is text. Also, you need to manage users pressing cancel button. With your variable declared as Date will give a Type Mismatch error.

Try these changes to your code & see if helps

Sub COT_Data_ETL()
    Dim RptDate As Variant
    Dim Institutions As Long
    
    Do
        RptDate = InputBox("Enter the date of the COT report in the format of MM/DD/YYYY.", "Report Date")
'cancel pressed
        If StrPtr(RptDate) = 0 Then Exit Sub
    Loop Until IsDate(RptDate)
    
    Institutions = WorksheetFunction.CountIf(Columns("I"), DateValue(RptDate))
    
    MsgBox Institutions & " Success!!!" & Chr(10) & _
    "The pivots and charts have all been refreshed and this workbook has been saved." & Chr(10) & _
    "If you're ready, copypaste the data you need into the Tracker" & Chr(10) & _
    "from the Pivot_for_Tracker tab." & Chr(10) & Chr(10) & _
    "Be sure to select the correct date in the slicer before copypasting. " & Chr(10) & _
    "Otherwise, you are done for now.", vbOKOnly, "COT Data ETL"
    
End Sub

Your variable is declared as variant to allow for other other data types (cancel being pressed).
Code checks if entry is a date & DateValue function coerces string to a date which hopefully, will give desired result.

Dave

 

[DavidT77]

Good point about pressing the Cancel button. I have that programmed for one of my IF-statements (not copied above) but missed it for my RptDate inputbox variable. I'll keep this handy for future reference.

I tried your code but it still came back with the same result of 0. But, i played around with it more and got it to work. I inserted the formula "WorksheetFunction.CountIf(Columns("I"), RptDate)" right into the MsgBox where the Institutions variable was and it worked. Go figure. Thanks for your help.