计数出现的VBA代码 [英] VBA code to count occurence

问题描述

我有一列A，它有一些元素，我需要计数出现并粘贴在D& E分别有姓名和计数。

由于列A中的元素不同，我无法使用我记录数据透视表的VBA代码。

任何人都可以帮助我有这个吗？

 名称数量
 AA 5 
 AB 3 
 AC 1 
 AD 1 
 B 
 B 
 C 
 B 
 D 
 A 
  

这是我试过的： -

  Sub Macro1（）
'
'Macro1宏
'
 
'
列（A：A）。选择
 ActiveWorkbook.PivotCaches.Create（SourceType ：= xlDatabase，SourceData：= _ 
Sheet1！R1C1：R1048576C1，版本：= xlPivotTableVersion14）.CreatePivotTable _ 
 TableDestination：=Sheet1！R1C4，TableName：=PivotTable1，DefaultVersion _ 
：= xlPivotTableVersion14 
她ets（Sheet1）。选择
单元格（1，4）。选择
使用ActiveSheet.PivotTables（PivotTable1）。PivotFields（Name）
 .Orientation = xlRowField 
 .Position = 1 
 End with 
 ActiveSheet.PivotTables（PivotTable1）。AddDataField ActiveSheet.PivotTables（_ 
PivotTable1）。PivotFields（Name），Count of名称，xlCount 
 End Sub 
  

解决方案

这个小宏尝试：

  Sub KountUneek（）
 Dim c As Collection，wf As WorksheetFunction，_ 
 K As Long，N As Long，I As Long，_ 
v As Variant 
 Set c = New Collection 
 Set wf = Application.WorksheetFunction 
 K = 1 
 N =单元格（Rows.Count，A）。End（xlUp）.Row 
 On Error Resume Next 
 
 For i = 1 To N 
v = Cells ，A）值
 c.Add v，CStr（v）
如果Err.Number = 0然后
单元格（K，B）。值= v 
单元格（K，C）Value = wf.CountIf（Range（A：A），v）
 K = K + 1 
 Else 
 Err .Number = 0 
 End If 
 Next i 
 On Error GoTo 0 
 End Sub 
  

例如：

I have column A which has some element I need to count the occurence and paste in column D & E with name and count respectively.
Since the elements in column A varies, I am unable to use the VBA code which I got for recording a Pivot Table.
Can anyone please Help me with this?

Name            Name    Count  
A                  A    5
A                  B    3
A                  C    1
A                  D    1
B               
B               
C               
B               
D               
A   

This is what I tried :-

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:A").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R1048576C1", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R1C4", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14
    Sheets("Sheet1").Select
    Cells(1, 4).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Name"), "Count of Name", xlCount
End Sub

解决方案

Give this small macro a try:

Sub KountUneek()
    Dim c As Collection, wf As WorksheetFunction, _
        K As Long, N As Long, i As Long, _
        v As Variant
    Set c = New Collection
    Set wf = Application.WorksheetFunction
    K = 1
    N = Cells(Rows.Count, "A").End(xlUp).Row
    On Error Resume Next

    For i = 1 To N
        v = Cells(i, "A").Value
        c.Add v, CStr(v)
        If Err.Number = 0 Then
            Cells(K, "B").Value = v
            Cells(K, "C").Value = wf.CountIf(Range("A:A"), v)
            K = K + 1
        Else
            Err.Number = 0
        End If
    Next i
    On Error GoTo 0
End Sub

For example:

这篇关于计数出现的VBA代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！