'根据单位进行累加,连接并未改变,,,
Option Explicit
Sub test()
Dim arr, mark, i, s, dic
Set dic = CreateObject("scripting.dictionary")
arr = Sheets("原始资料").[a1].CurrentRegion.Offset(1)
ReDim brr(1 To 1, 1 To 3)
With Sheets("查询")
mark = .[e2:h2].Value
For i = 1 To UBound(arr, 1) - 1
If mark(1, 1) = arr(i, 8) And arr(i, 4) >= mark(1, 2) And arr(i, 4) <= mark(1, 4) Then
brr(1, 1) = arr(i, 6): brr(1, 2) = arr(i, 7)
dic(arr(i, 11)) = dic(arr(i, 11)) + arr(i, 10)
s = s & "+" & "(" & arr(i, 4) & "-" & arr(i, 22) & ")" & arr(i, 10) & arr(i, 11)
End If
Next
If Len(s) > 0 Then
For Each i In dic.keys
brr(1, 3) = brr(1, 3) & "+" & dic(i) & i
Next
brr(1, 3) = "总预订数=其中:" & Mid(brr(1, 3), 2)
brr(1, 3) = brr(1, 3) & vbNewLine & "其中:" & Mid(s, 2)
End If
.[a6].Resize(, 3) = brr
End With
End Sub |