+ Reply to Thread
Results 1 to 15 of 15

VBA to count stored array string in range

  1. #1
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    VBA to count stored array string in range

    Hi all

    I have started another thread as the previous had been solved and now the criteria has become more elaborate.
    With my attached sample file.
    I need to go through Sheets("Customer sales").Column B and record the no of different suppliers in Sheets("Product breakdown") Column C.

    There are various products, however they each belong to a specific supplier....I have made use of keywords as per Sheets("Product breakdown Description")
    Sheets("Customer sales").Column's C & D are edited to depict the breakdown.

    I am not certain what procedure to make use of as the current code counts 13 as per individual highlights in Sheets("Customer sales").Column B.
    the correct answer should be 6 as the breakdown is 6 Suppliers.

    Can anyone please put me out of my misery....

    I thank you in advance.
    Attached Files Attached Files
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Have solved with multiple if statements but there must be a simpler solution...
    Please Login or Register  to view this content.
    Last edited by sintek; 05-22-2017 at 09:55 AM.

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Hi All

    The code is producing inaccurate readings some times....Any ideas?

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    I have uploaded a sample which is not giving the correct results.....
    Have used the below code in sample as the criteria is different:
    I just can't understand how to solve this problem.... feel hopeless!!!!!

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Came up with this which works but must be an easier way
    Please Login or Register  to view this content.
    Last edited by sintek; 05-23-2017 at 05:41 AM.

  6. #6
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA to count stored array string in range

    With the space after SP



    Kind regards
    Leo
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool

    Hi !

    Quote Originally Posted by sintek View Post
    the correct answer should be 6 as the breakdown is 6 Suppliers.
    According to your attachment try this demonstration !

    PHP Code: 
    Sub Demo()
        
    Sheet2.[C3].Value Application.CountA(Sheet3.UsedRange.Columns(2)) - 1
    End Sub 
    Do you like it ? So thanks to click on bottom left star icon « Add Reputation » !

  8. #8
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    @ Leo
    Your code works as per your file uploaded [Tester 24-05-2017] pertaining to my previous thread:https://www.excelforum.com/excel-pro...ml#post4659932

    However as per upload in Post4 of this thread, the criteria has changed quite drastically....

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    @ MarcL
    Code works perfectly if I had Col B as a helper column....Unfortunately that is not the case in the actual file.
    There are several such Headers with multiple Keywords.

  10. #10
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA to count stored array string in range

    How does sheet with headers look in actual file ?


    Cheers
    Leo

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Herewith actual Headers with Helper Keywords....
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA to count stored array string in range

    Have result of 5


    Kind regards
    Leo
    Attached Files Attached Files

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Hi Leo

    Absolutely love your code....Don't yet understand it...but will get there...
    Thank you so much for your time and effort you have put into this...Close but no cigar.
    I have uploaded a tester with your code with the required result in [Product Breakdown]
    Have made notes in [Customer sales] as to the required categories.
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: VBA to count stored array string in range

    Hi Sintek,

    result is different, remarks in sheet Customer Sales



    Kind regards
    Leo
    Attached Files Attached Files

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to count stored array string in range

    Leo....You are an absolute Genius.....
    Yes you are correct in identifying score as ChillBev
    Perfection seriously.....Thank you so so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count of Times a String Appears in an Array
    By mikealoni in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-07-2014, 11:34 AM
  2. VBA Find Partial String in String Array and Output the Found String Array Value
    By scherich in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2014, 11:20 AM
  3. [SOLVED] Return matching string in array if cell contains string contained in the array
    By AaronsZ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-03-2013, 01:55 PM
  4. How to delete block of elements from stored VBA array string
    By nuttyengineer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-27-2013, 07:06 PM
  5. [SOLVED] Range to array, then check to see if the array contains a string
    By yeahyeahyeah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2012, 12:17 PM
  6. Lookup using match in named range stored in array
    By erikhs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2007, 06:06 AM
  7. Can't programmatically set a large array string to a range.
    By Lu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2006, 10:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1