+ Reply to Thread
Results 1 to 3 of 3

Count cells in a dynamic range, calculate their percentage in respect to total cells.

  1. #1
    Registered User
    Join Date
    07-19-2018
    Location
    London
    MS-Off Ver
    2016
    Posts
    1

    Count cells in a dynamic range, calculate their percentage in respect to total cells.

    Hi all,

    I'm quite new in programming and i have this question.


    There are 3 columns (from left to right):
    1) IPM Project Name
    2) EAN CODE
    3) Feasibility: each EAN CODE has feasibility right next to it. It could be "Matched", "Matched & Robust" , NA etc

    My code:
    starts from cells(2,1) with rng as Range, moves right until it finds the EAN CODE column and moves downwards, until it finds a cell which is not empty.
    When it does, moves left until it finds the IPM Project name column.


    IPMrng asRange


    What i intend to achieve:
    move down, and for all project names that are the same ( IPMrng.offset(1,0) ), look into the range.offset(0,1) which is the Feasibility column and count how many times "Matched" or "Matched & Robust" categories occur for that IPM Project Name.
    If they occur >50% of the Total categories in the Feasibility column, then type next to it rng.offset(0,2) something like Yes, or No.


    The Count process is not very clear to me and it is definitely wrong. Any help would be highly appreciated.

    Note: I typed rng.select several time to visualize the whole process while stepping through the code. Also added Message Boxes to see how the code works/


    Attachment 611875


    Please Login or Register  to view this content.
    'Counts the various Statuses
    If UCase(ActiveSheet.Cells(rng.Row, rng.Column)) Like "*Matched*" Or UCase(ActiveSheet.Cells(rng.Row, rng.Column)) Like "*Matched & Robust*" Then

    i = i + 1
    MsgBox i

    Else
    j = j + 1
    MsgBox j

    End If

    Set IPMrng = IPMrng.Offset(1, 0)
    IPMrng.Select

    Loop

    Loop


    ' Checks if the sum of (Matched + Matched & Robust) is ** Greater Than 50% ** of the total status
    ' If Yes, then Report
    ' If No, then Do not Report

    If i >= 0.5 * j Then

    rng.Offset(0, 3) = "Yes"

    Else

    rng.Offset(0, 3) = "No"

    End If

    End If

    End If

    Loop



    End Sub[/CODE]
    Attached Files Attached Files
    Last edited by DimitriosNikolouzos; 02-19-2019 at 06:27 AM. Reason: added a sample file

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    29,916

    Re: Count cells in a dynamic range, calculate their percentage in respect to total cells.

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor banaanas's Avatar
    Join Date
    08-26-2014
    Location
    Finland
    MS-Off Ver
    2016
    Posts
    198

    Re: Count cells in a dynamic range, calculate their percentage in respect to total cells.

    This was again a neat project. I am not totally happy about my code (it needs to loop the same range twice) but it seems to work fine.
    I am sorry that I started the code from scratch, But it was just a lot easier than try to modify your take

    See attachment workbook below, or copy the code. Please note that you need to add Microsoft Scripting Runtime from Tools -> References for the code to work.
    I use dictionaries to store Project information.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Tuomas "Banaanas" Savonius
    Trying to give back now when I actually can do some VBA

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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