VBA Code help to count no. of occurrences per month

[soph33]

Hello All,

I am getting gray hair over this one. With the aid of a Macro, I was able to generate the following code to count the amount of incidents by month:

Please Login or Register  to view this content.

At first I was having issues with the SUMPRODUCT statements, as I did realize I had to de-increment the RC references. OK. I figured that one out and it is working now, i.e. I can get the code to do what it is supposed.

My main issue now is that I realized that I would virtually have to add a new line of code for every new month in the future, i.e. do the next 5 years in advance, so that I do not have to constantly update the code.

I was wondering if anyone new of a more elegant way to solve the issue?

Column E contains information on the Log Date of Support cases. I have formatted the column to display, as follows "Mar-98".

I need code, so that excel will automatically generate 2 rows in sheet3. 1st row containing month and year, denoted as above and the 2nd row containing the sum of occurrences.

Ideally the code will automatically recognize the (new) month and year and automatically generate the according header in the cell above. I've attached the table for your reference:

I highly appreciate any help in advance. Please let me know, if you require any additional information.

Thx,

Soph.

[Leith Ross]

Hello soph33,

Welocme to the Forum!

To make your posts easier to read, copy, and edit please wrap your code. I did it for you this time. Here is how you can do it next time.

How to wrap your Code
1. Select all your code using the mouse.
2. Click on the # icon on the toolbar in the Message window. This will automatically wrap the text you selected with the proper Code tags to create a Code Window in your post.

OR you can also do this Manually by placing the tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] at the end of the last line.


As a member, You have agreed to follow the forum posting rules. Please take so time to familiarize yourself with the Do's and Don'ts here in the Forum, just click on the link below...

Forum Rules

[soph33]

Leith,

Thanks for the pointers. I am sorry and will in future take care to abide by the rules.

Soph.