Help with using VBA to count number of times different values appear in a column

[compto]

Hi,

I am struggling to come up with a solution to my problem. I have a list of, for example, fruit and the names of these fruit appear more than once in a list. I do not know the names of the fruit prior to executing a piece of code so cannot lookup from anywhere. I have produced the following code which works, except it includes the name of the fruit in cell C2 and it's corresponding count twice. Code:

Sub CalcTotalFruit()
Worksheets("Fruit").Activate
With ActiveSheet
   .Range("C2", Range("C2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "G2"), Unique:=True
    .Names("Extract").Delete
Dim lrC As Long, lrG As Long
lrC = Cells(Rows.Count, "C").End(xlUp).Row
lrG = Cells(Rows.Count, "G").End(xlUp).Row
Range("H2:H" & lrG).Formula = "=COUNTIF($C$2:$C$" & lrC & ",$G2)"
End With
End Sub

I have also attached sample data.

If anyone can help me understand where I am going wrong I would be grateful. Also suggestions on how to improve the code in general is welcome!

Thanks

Book1.xls

[MarvinP]

Hi compto and welcome to the forum,

I think this is an easy Pivot Table answer. See the attached that does a quick pivot table of your data with counts.

[compto]

Thanks for the welcome MarvinP and for the suggestion. A PivotTable was my first idea but unfortunately I need this piece of code for about 15 different worksheets in the same workbook and when I had a pivot table on every sheet the file size ballooned and made it very slow. So I thought a piece of code would be more efficient unless I'm mistaken?

[mmmarks]

Hi,

I am struggling to come up with a solution to my problem. I have a list of, for example, fruit and the names of these fruit appear more than once in a list. I do not know the names of the fruit prior to executing a piece of code so cannot lookup from anywhere. I have produced the following code which works, except it includes the name of the fruit in cell C2 and it's corresponding count twice. Code:

Sub CalcTotalFruit()
Worksheets("Fruit").Activate
With ActiveSheet
   .Range("C2", Range("C2").End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
        "G2"), Unique:=True
    .Names("Extract").Delete
Dim lrC As Long, lrG As Long
lrC = Cells(Rows.Count, "C").End(xlUp).Row
lrG = Cells(Rows.Count, "G").End(xlUp).Row
Range("H2:H" & lrG).Formula = "=COUNTIF($C$2:$C$" & lrC & ",$G2)"
End With
End Sub

I have also attached sample data.

If anyone can help me understand where I am going wrong I would be grateful. Also suggestions on how to improve the code in general is welcome!

Thanks

Attachment 191457

Hi,

Look @ sheet name once whether sheet name has mismatch with code or having spaces... again and try it...

[compto]

The sheet name is fine and cannot see any problem with spacing either. The code works if the fruit in cell C2 does not appear again and count returns as "1" like it should. The problem only occurs when the fruit in the first cell of the list, C2, appears again further down the list.

[compto]

I have also come across another problem. When there are blank cells at the top of the list (as there will be on occassion in my full model) the code falls over so it is clearly not robust. Can anyone suggest any tweaks or a better way to achieve what i want? File with the new error attached also. Thanks
Book2.xls

[MarvinP]

Hi compto,

Look at the attached that uses a Dynamic Named Range and will count the number of fruits you have...

[compto]

Thanks MarvinP, I have a solution for what I wanted now which doesn't use a DNR but I did learn something new from what you posted so I appreicate your time!