|
Private Sub Worksheet_Activate()
Call tt
With Cells(1, 3).Validation
.Delete
For R = 2 To UBound(Ar)
D(Ar(R, 17)) = ""
Next R
Ky = D.Keys
Str = VBA.Join(Ky, ",")
.Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:=Str '来自字符串
End With
D.RemoveAll
With Cells(2, 3).Validation
.Delete
For R = 2 To UBound(Ar)
D(Ar(R, 9)) = ""
Next R
Ky = D.Keys
Str = VBA.Join(Ky, ",")
.Add Type:=3, AlertStyle:=1, Operator:=1, Formula1:=Str '来自字符串
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Cells(1, 2).Address Or Target.Address = Cells(1, 3).Address Or Target.Address = Cells(2, 3).Address Then
Call 销售表
End If
End Sub
Public Ar, Br, Cr(), R&, C&, K&, I&, Str$
Public D As Object
Public Sub tt()
Set D = CreateObject("scripting.dictionary")
Ar = Sheets("report").[a1].CurrentRegion
Br = Sheets("销售").Range("A1:O2")
K = 0
End Sub
Public Sub 销售表()
Call tt
On Error Resume Next
Sheets("销售").Range("A4").CurrentRegion.Offset(1, 0).ClearContents
For R = 2 To UBound(Ar)
If InStr(Br(1, 2), Ar(R, 6)) And Ar(R, 17) = Br(1, 3) And Ar(R, 9) = CDate(Br(2, 3)) Then
If D.exists(Ar(R, 5)) Then
I = D(Ar(R, 5))
Cr(3, I) = Cr(3, I) + Ar(R, 25)
Cr(4, I) = Cr(4, I) + Ar(R, 32)
Cr(5, I) = Application.Round(Cr(4, I) / Cr(3, I), 2)
Else
K = K + 1
D(Ar(R, 5)) = K
ReDim Preserve Cr(1 To 5, 1 To K)
Cr(1, K) = K
Cr(2, K) = Ar(R, 5): Cr(3, K) = Ar(R, 25)
Cr(4, K) = Ar(R, 32): Cr(5, K) = Application.Round(Cr(4, K) / Cr(3, K), 2)
End If
End If
Next R
With Sheets("销售")
.Range("A5").Resize(K, 5) = Application.Transpose(Cr)
R = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(R, 2) = "合计"
.Cells(R, 3) = Application.Sum(Range("C5:C" & R))
.Cells(R, 4) = Application.Sum(Range("D5:D" & R))
End With
End Sub |
-
|