Excel 2007 : Left Command

[colbyhynes]

Hello Scholars!
I am having a bit of trouble grabbing the data I want. Can you help?

In short, I have a sheet containing completed invoices. Each invoice has a date.
I want to count all instances of the month September (as specified in a drop-down list) in order to find out how many invoices were received in September.

The sample range I have selected has only dates beginning with "2011 09 ", therefore they should all increment my counter by 1.

However, it is not working



A Sample of my Code:

Please Login or Register  to view this content.

Any suggestions?

[Paul]

Hi Colby, welcome to the forum.

Have you tried changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If that doesn't fix it, please post a sample workbook.

[colbyhynes]

Thanks Paul, I have tried this, however no luck as of yet.
I have uploaded a sample workbook as requested. Thanks very much, your input is greatly appreciated!

Hi Colby, welcome to the forum.

Have you tried changing:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If that doesn't fix it, please post a sample workbook.

[Paul]

Do you need to use all that code in the first place? If you do the following, you won't have to Select, Loop, or any of that.

1. On the Lists tab, change all of the values to actual dates, e.g. 1/1/2011, 2/1/2011 (for January 2011, February 2011), etc. Then format those cells as Custom using MMMM YYYY.

2. On the Invoice_Completion_Data tab, format cell J18 as Custom using MMMM YYYY.

3. Change your code to a simple VBA COUNTIF (you could do it using a formula and no macro if you wanted..

Please Login or Register  to view this content.

You can then select a month/year from the drop-down, run the macro and it will show your count in J20.

You can also change this so that this code is in the Invoice_Completion_Data worksheet's code module in the Worksheet_Change event and check to see if you changed cell J18. That way anytime you change that cell it will re-calc J20. For example:

Please Login or Register  to view this content.

The formula method.. instead of using code at all (just make changes in steps 1 & 2 above for dates)..

In J20 just add the formula:

=COUNTIF(TEST_Sept_11,YEAR(J18)&" "&TEXT(MONTH(J18),"00")&"*")

Hope that helps!