Countif in VBA msgbox using a variable date as the criteria

DavidT77

New Member
Joined
Mar 23, 2017
Messages
8
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.
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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

Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top