基于列A的总和列B使用Excel VBA宏 [英] Sum Column B based on Column A using Excel VBA Macro

问题描述

好的，我有一个简单的问题，我需要帮助在一个VBA宏。我有一个excel表格，看起来像这样...

 产品＃Count 
 101 1 
 102 1 
 101 2 
 102 2 
 107 7 
 101 4 
 101 4 
 189 9 
  pre> 
 
 
我需要一个根据产品编号列添加计数列的宏。我想要它完成后，看起来像这样... 
 产品＃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屋！