Dynamic count of elements vba

pchatziko

New Member
Joined
Apr 30, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Based on the idea of this thread

i expanded the idea from a fixed 3 elements to any number of elements, now i want to count their total appearances as max but with one caveat, if we have more than 1 max in the row then the count of each element want to be counted / the number of max, as per the image below
1620091657700.png


I created an array with all the matched max and then i created this code to do it for 3 elements which works fine but it is obviously a very dumb way of doing it.
VBA Code:
Dim CA as Double
Dim CB as Double
Dim CC as Double
CA=0
CB=0
CC=0
For i = 1 To UBound(arrcount)
For j = 2 To 2
If arrcount(i, j) = "" Then
    If arrcount(i, j - 1) = "A" Then
    CA = CA + 1
    ElseIf arrcount(i, j - 1) = "B" Then
    CB = CB + 1
    Else
    CC = CC + 1
    End If
ElseIf arrcount(i, j + 1) = "" Then
    If arrcount(i, j - 1) = "A" Then
    CA = CA + (1 / 2)
    ElseIf arrcount(i, j - 1) = "B" Then
    CB = CB+ (1 / 2)
    Else
    CC = CC + (1 / 2)
    End If
     If arrcount(i, j) = "A" Then
    CA= CA + (1 / 2)
    ElseIf arrcount(i, j) = "B" Then
    CB = CB + (1 / 2)
    Else
    CC = CC+ (1 / 2)
    End If
Else
CA = CA + (1 / 3)
CB = CB+ (1 / 3)
CC = CC + (1 / 3)
End If
Next j
Next i

Thanks again for the wisdom you are sharing.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,148,244
Messages
5,745,596
Members
423,964
Latest member
Rayds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top