基于列A的总和列B使用Excel VBA宏 [英] Sum Column B based on Column A using Excel VBA Macro
问题描述
产品#Count
pre>
101 1
102 1
101 2
102 2
107 7
101 4
101 4
189 9
我需要一个根据产品编号列添加计数列的宏。我想要它完成后,看起来像这样...
产品#Count
101 7
102 7
107 7
189 9
我是一个到VBA,所以我会喜欢任何帮助我可以得到。
解决方案假设数据在列A和B中,你可以做它有一个公式:
= SUMIF(A:A,101,B:B)
或者如果将101放在C1中:
= SUMIF(A:A,C1,B:B)
/ strong>
但是,如果您仍然需要VBA,这里是我的(快速和肮脏)提案 - 我使用字典来跟踪每个项目的总和。 / p>
Sub doIt()
Dim data As Variant
Dim i As Long
Dim countDict As Variant
Dim category As Variant
Dim value As Variant
Set countDict = CreateObject(S cripting.Dictionary)
data = ActiveSheet.UsedRange'假设数据在列A / B
'填充字典:key = category / Item = count
对于i = LBound(数据,1)到UBound(数据,1)
category = data(i,1)
value = data(i,2)
如果countDict.exists类别)然后
countDict(category)= countDict(category)+ value'如果我们已经看到那个类别,添加到总
Else
countDict(category)= value'第一次我们找到该类别,创建它
End If
Next i
'将字典复制到数组
ReDim数据(1到countDict.Count,1到2)As变量
Dim d As Variant
i = 1
对于每个d在countDict
数据(i,1)= d
数据(i,2) = countDict(d)
i = i + 1
下一步d
'将结果放回D / E列中的表格,包括标题
With ActiveSheet
.Range(D1)。调整大小(UBound(data,1),UBou nd(data,2))= data
End with
End Sub
OK, I have a simple problem that I need help with in a VBA Macro. I have an excel sheet that looks like this...
Product # Count 101 1 102 1 101 2 102 2 107 7 101 4 101 4 189 9
I need a macro that adds up the "count" column based on the Product Number Column. I want it to over all look like this after I am done...
Product # Count 101 7 102 7 107 7 189 9
I am an amiture to VBA so I would love any help I can get.
解决方案Assuming the data is in columns A and B, you can do it with a formula:
=SUMIF(A:A,101,B:B)
Or if you put 101 in C1:
=SUMIF(A:A,C1,B:B)
EDIT
However if you still require VBA, here is my (quick and dirty) proposal - I use a dictionary to keep track of the sum for each item.
Sub doIt() Dim data As Variant Dim i As Long Dim countDict As Variant Dim category As Variant Dim value As Variant Set countDict = CreateObject("Scripting.Dictionary") data = ActiveSheet.UsedRange 'Assumes data is in columns A/B 'Populate the dictionary: key = category / Item = count For i = LBound(data, 1) To UBound(data, 1) category = data(i, 1) value = data(i, 2) If countDict.exists(category) Then countDict(category) = countDict(category) + value 'if we have already seen that category, add to the total Else countDict(category) = value 'first time we find that category, create it End If Next i 'Copy dictionary into an array ReDim data(1 To countDict.Count, 1 To 2) As Variant Dim d As Variant i = 1 For Each d In countDict data(i, 1) = d data(i, 2) = countDict(d) i = i + 1 Next d 'Puts the result back in the sheet in column D/E, including headers With ActiveSheet .Range("D1").Resize(UBound(data, 1), UBound(data, 2)) = data End With End Sub
这篇关于基于列A的总和列B使用Excel VBA宏的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!