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]

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.

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.