COUNTA AND COUNTIF in a macro

**itsnick** · 10-28-2009, 10:48 AM

What I'm trying to do is add a line where it counts the number of cells with either the word "NEW" in a certain row (Countif) or the number of cells with a value in a range(Counta). CountA is not working because if I run the macro twice it adds a second total line and messes both the count and total up. COUNTIF I just cant get to work because im a VBA SUPER NOOB and im just pieceing code together from this forum and the net.

Heres an example of what id like to do.

Please Login or Register  to view this content.

Current version of this macro.

Please Login or Register  to view this content.

**itsnick** · 10-28-2009, 03:33 PM

Do I need to clarify anything looking for some help.

**broro183** · 10-28-2009, 05:25 PM

hi,

Here's what the macro recorder gives me for entering the Count functions...

Please Login or Register  to view this content.

but then comes your comment "if I run the macro a second time"...

Do you need to keep the totals separate each time the macro is run?
If not, I strongly recommend moving the total cells to above the header row (& using a dynamic named range). This means the total becomes part of the original design & doesn't need to be modifed each time the macro is run.
If you do, you could try this approach:

Please Login or Register  to view this content.

hth
Rob

**broro183** · 10-28-2009, 06:14 PM

hi again,

I've played around a little & have come up with the below (please note that I haven't thoroughly tested it). Also, I haven't included all your formatting code but I think you'll be able to add this into the right place. :-)

Please Login or Register  to view this content.

hth
Rob

**itsnick** · 10-29-2009, 09:06 AM

Rob,

Thanks for all your help it seems this changed the format/style of my current macro and I was able to merge the two. I see what you did with the totals and that works grea, but im going to go with your suggestion and create a totals area. It just makes more sense, for those reading this regarding the countif or counta, I added a seperate summary sheet with the a simple countif formula. Once again thank you for your help.

**broro183** · 10-29-2009, 04:51 PM

hi Nick,

Thanks for the feedback - I'm pleased I could help

I'm stoked you went with my suggestion for changing your file layout because (although we can modify macros) I think it is much easier to maintain a consistent header row area.

Would you mind posting your completed file in case others are searching the thread?

Feel free to add to my reputation by clicking on the blue scales at the top of my post - it is appreciated

Rob