第1个回答 推荐于2016-03-20
Sub 查找()
Dim arr, dic As Object, i%, RangA As Range
Set RangA = Range("g4", "i100")
arr = RangA
Set dic = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(arr)
If dic.exists(arr(i, 2)) Then
dic(arr(i, 2)) = dic.Item(arr(i, 2)) & "、" & arr(i, 3)
Else
dic(arr(i, 2)) = dic.Count + 1 & "|" & arr(i, 2) & "|=SUMIF(H:H,B" & dic.Count + 4 & ",I:I)|" & arr(i, 3)
End If
Next i
dic.Remove ""
Set RangA = Range("a4")
RangA.Offset(0, 0).Resize(dic.Count, 1) = Application.Transpose(dic.Items)
Application.DisplayAlerts = False
RangA.Offset(0, 0).Resize(dic.Count, 1).TextToColumns Other:=True, OtherChar:="|"
Application.DisplayAlerts = True
Set dic = Nothing
End Sub本回答被提问者采纳
第2个回答 推荐于2016-08-05
Sub 条件统计()
Dim refg As String
refg = ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count).Row
Range("B3:C4") = 0
For R = 3 To 4
For C = 2 To 3
For i = 3 To refg
If Cells(R, 1) = Cells(i, 5) And Cells(2, C) = Cells(i, 6) Then
Cells(R, C) = Cells(R, C) + Cells(i, 7)
End If
Next
Next
Next
End Sub