VBA用两种方法实现计算一列结果

laoau138

VBA用两种方法实现计算一列结果

yanhj

Private Sub CommandButton1_Click()
For i = 3 To Range("D65536").End(3).Row
   For j = 3 To Range("A65536").End(3).Row
      If Cells(i, 4) = Cells(j, 1) Then
       Cells(i, 5) = Cells(i, 5) + Cells(j, 2)
      End If
   Next
Next
End Sub

zhaoyunhai

ＶＢＡ学习中，慢慢理解吧

yanhj

Private Sub CommandButton1_Click()
  Dim dic As Object, i%
    Set dic = CreateObject("scripting.dictionary")
    For i = 3 To Cells(Rows.Count, 1).End(3).Row
      If dic.exists(Cells(i, 1).Value) Then
      dic(Cells(i, 1).Value) = dic(Cells(i, 1).Value) + Cells(i, 2).Value
      Else
      dic(Cells(i, 1).Value) = Cells(i, 2).Value
      End If
    Next i
    Range("D:E").Clear
    Cells(2, 4).Resize(1, 2) = Array("编号", "金额")
    Cells(3, 4).Resize(dic.Count, 1) = Application.Transpose(dic.keys)
    Cells(3, 5).Resize(dic.Count, 1) = Application.Transpose(dic.items)
    Set dic = Nothing
End Sub

大灰狼1976

如果编号是1开始连续的纯数字的话，直接

1. Sub aaa()
   
2. Dim arr, brr, i&
   
3. arr = [a1].CurrentRegion
   
4. ReDim brr(1 To Application.Max(Columns(1)), 1 To 2)
   
5. For i = 3 To UBound(arr)
   
6.   brr(arr(i, 1), 1) = arr(i, 1)
   
7.   brr(arr(i, 1), 2) = brr(arr(i, 1), 2) + arr(i, 2)
   
8. Next i
   
9. [d3].Resize(UBound(brr), 2) = brr
   
10. End Sub

复制代码