Adding a countIf function into column B if A has data

Hi All,

I am I have written a macro that transposes all the rows of a table into one column and then adds a year in the column next to it going to the next given the data in the column was originally from the next row.

Basically it does this:

2003 01 02 03 04 05 06 07 08 09 10
2004 11 12 13 14 15 16 17 18 19
2005 20 21 22 23 24 25 26 27
.....
.....
2011 65 66

2003 01
2003 02
2003 03
......
......
......
2004 11
2004 12
......
2005 21
2005 22
......
etc

obviously the columns dont have dots but the respective data, Im just being lazy haha

anyway heres the code and my question

Please Login or Register  to view this content.

I have tried to add another column that will have the CountIf function and basically do CountIF($A1:A$1,A1) thus assigning a number next to the year so like this

ultimately I want the data to look like this

2003 1 01
2003 2 02
2003 3 03
......
......
......
2004 1 11
2004 2 12
......
2005 1 21
2005 2 22
......

It is easy to set the COUNTIF function to this in excel but I am struggling to see how I will complete the vba code for this as I need it on many sheets and many tables.

I hope this explanation is clear and I appreciate any help!

Thank you very much Jakobshavn! This does to what I am looking for! I am now just looking to break down my macro into smaller ones given what you have given me.
Thanks again, much appreciated!

One last thing if I may. I have added some code to get this to work on all sheets, Ive used this before for other macros but it doesnt seem to be working here. Any ideas why?

Please Login or Register  to view this content.

haha how silly of me!

Thanks again Jakobshavn!