|
回复 yangxmqj 的帖子
框框不用那么多嘛。b3放的是开始日期,b4放的是结束日期,b5放的是金额:
Sub hz()
Dim sjy(), jg()
Dim sdate As Date, edate As Date, je As Double
Dim d As Object, r1 As Integer, r2 As Integer
Set d = CreateObject("scripting.Dictionary")
With Sheet1
r1 = .Cells(.Rows.Count, 1).End(xlUp).Row
sjy = .Range("a2:c" & r1).Value
ReDim jg(1 To r1, 1 To 3)
End With
With Sheet2
sdate = .[b3].Value
edate = .[b4].Value
je = .[b5].Value
For r1 = 1 To UBound(sjy)
If sjy(r1, 2) >= sdate And sjy(r1, 2) <= edate Then
If Not d.Exists(sjy(r1, 1)) Then d(sjy(r1, 1)) = d.Count + 1
jg(d(sjy(r1, 1)), 1) = sjy(r1, 1)
jg(d(sjy(r1, 1)), 2) = sjy(r1, 2)
jg(d(sjy(r1, 1)), 3) = sjy(r1, 3) + jg(d(sjy(r1, 1)), 3)
End If
Next
For r1 = 1 To d.Count
If jg(r1, 3) >= je Then
r2 = r2 + 1
jg(r2, 1) = jg(r1, 1)
jg(r2, 2) = jg(r1, 2)
jg(r2, 3) = jg(r1, 3)
End If
Next
.Rows("10:65536").ClearContents
.[b10].Resize(r2, 3) = jg
End With
End Sub
重复发贴看遭罚[em07]
|
|