计数出现的VBA代码 [英] VBA code to count occurence
本文介绍了计数出现的VBA代码的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
由于列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屋!
查看全文