Counting quantity - data contained in one cell by new line

  • [xpost][/xpost]

    mrexcel.com/board/threads/coun…cell-by-new-line.1178624/


    Hello,


    Thank you in advance to answer my question!


    I'm a beginner in VBA and i'm working on some code that can help me out to count the total number of specific product from the cell, for example in column B it contained different shoes brand with quantity we sold, i want to know the total number of adidas we sold on the entire column B, so I select the option box says adidas then click on count button, it will then give me the result of 6 next to Label1, below is my code but seems not work.


    Please note that the data that i received is system generated and data are contained in one cell by new line, so that is complicated for me.

    Please also see attachment of how my userform look like.


    Thanks and appreciate that!

  • It is hard to work with a picture. Could you attach a copy of your file? It would be easier to see how your data is organized and to test possible solutions. Include a detailed explanation of what you want to do using a few examples from your data and referring to specific cells, rows, columns and sheets. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks Mumps!


    I have attached the file.


    Basically my vba code is counting the total specific product in column B, for example:

    (1) Run VBA Code

    (2) Click on Option button to select Adidas

    (3) Click on Count Button

    (4) Result will show 3 which is because 3 is just a total of "Adidas word" in column B, i wants the result counting how many Adidas we sold in column B which is 6


    Please see attached excel, thanks in advance for your help!

  • I have requested MrExcel to remove my thread due to cross posting, and i don't know why i can't edit above question so that i updated in here!


    Thanks Mumps!


    I have attached the file.


    Basically my vba code is counting the total specific product in column B, for example:

    (1) Run VBA Code

    (2) Click on Option button to select Adidas

    (3) Click on Count Button

    (4) Result will show 3 which is because 3 is just a total of "Adidas word" in column B


    What i wants the code can help me:

    - Counting the total number of Adidas we sold in column B which is 6

    - How to write the code for searching specific product by selecting on different option button, for example if i select Nike then click on count, and the result will show me 6


    Please see attached excel, thanks in advance for your help!

  • Try the attached file. Click the "Count Quantity" button, then select a product and click the "Count" button. After selecting the first product, you can select a different product, click the "Count" button again and get the count for the new product. Click the "Exit" button when finished.

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Hi Mumps,


    Thank you for spending time for work out the solutions for me!


    I have play around the file and it works perfectly but if i deleted one entry says "Nike" at B3 and then there will be an error (please see attached), sorry i didn't make it clear at the beginning, the report is system generated that could be more or less entry on each cell, could you please modify the code?


    And may i know what the below code means? is it reflected to the number of option button i have? if i have 10 option button, do i just change to 1 to 10 ?


    Code
    For i = 1 To 5


    Many thanks in advance!

  • if i have 10 option button, do i just change to 1 to 10 ?

    That is correct.


    When you delete "Nike" at B3, you will notice that a blank like remains between "Adidas" and "New Balance". Delete that blank line. Deleting the blank line that results when you delete any entry, should fix the error problem.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • My pleasure. :)

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!