Count if, display results in msgbox

[j9070749]

Hi all, I am unsure if this will be easier if I just used a formula and displayed the results in a cell. But here is what I wish to achive:

In cells D:D, I have a 'loop refrence' which can be A, B, C, D or E. The data is inserted sequentially so A,A,A,A,A,A,B,B,B,C,C,C,C,C. The refrence could finish at any letter, so it may only have two loops, so column D:D may consist of A,A,A,A,A,A,B,B,B,B,B. Hope this is making sence so far.

In loop A, 1 piece of equiptment is stored per cell in column M:M e.g.
A, MAC
A, Smoke
A, Sounder
A, MAC
A, MAC

I want a messagebox to display how many 'MACs', 'Smoke', and 'Sounders' are in each seperate loop. e.g.
Loop A
34 MACs
3 Smokes
2 Sounders

LoopB
2 MACs
6 Smokes
7Sounders

Hope this is clear enough, thanks in advance for any help!

[j9070749]

Has anyone got any suggestions?

Thanks

[j9070749]

Could anyone suggest a way or starting this off? Or how to count IF in VBA code, thanks.

[j9070749]

any suggestions at all are welcome

[jeffreybrown]

Hi all, I am unsure if this will be easier if I just used a formula and displayed the results in a cell.

I wouldn't mind helping, but can you supply a sample workbook and at least for me, it may be easier to display in a cell versus a message box.

On second thought, have you considered a pivot table?

[HSV]

Maybe you could upload an example.

[j9070749]

I don't have the spreadsheet I'm working on, but I have attaches an example one. Hope this makes it easier to understand.

Thanks

[FDibbins]

see if the attached is something you can work with?

it does not use macros, and the "loop" list will grow as you add more

edit: revised by adding a pivot table as a 2nd alternative

[jeffreybrown]

See how this works for you and a big thanks to Moo for giving me a helping hand (and a good set of eyes).

[DonkeyOte]

Another method for building this type of table is to use the Dictionary Object, e.g:

Please Login or Register  to view this content.

You could iterate the Dictionary and write straight back to Sheet rather than use Array, however, less to / fro Worksheet / VBE using the Array (ie single update).

[JBeaucaire]

My take on Jeffrey's "use a formula" method, I like it because it's loopless and uses the autofilter:

Please Login or Register  to view this content.

[jeffreybrown]

Well Merry Christmas to me and the OP and thank you so much for the assist. I woke up this morning not knowing what to expect and as usual you guys came through with sharing your experience/knowledge.

@Luke, I've studied the scripting dictionary and love it, but at the same time, always confused by it. I've stepped through your sample quite a few times and it is sinking in ever so slowly. When first running the example it seems a variable was missed in the declaration, v, so I added it as a variant. Is this the correct declaration?

@Jerry, I initially looked into the advanced filter, but when two columns where involved I scraped the idea, but now seeing how you used the concatenate and THEN the advanced filter, I should have thought longer and harder. It seems you may have left a piece of testing code behind in the form of...

Please Login or Register  to view this content.

Thanks again to all. I have learned so much from you and hope to continue...

God Bless and Merry Christmas to all...

[j9070749]

Thankyou very much for the help all the solutions work great!

[jeffreybrown]

You are most welcome. We are all glad to have helped and happy you have a solution to work with.

[DonkeyOte]

@Luke, I've studied the scripting dictionary and love it, but at the same time, always confused by it. I've stepped through your sample quite a few times and it is sinking in ever so slowly. When first running the example it seems a variable was missed in the declaration, v, so I added it as a variant. Is this the correct declaration?

Error on my part - I modified my original (stored .bas) but failed to modify the Variant from vStr to v so yes the above is correct. Apologies for oversight but good to know people are running Option Explicit

[jindon]

Try

Please Login or Register  to view this content.