|
1、在工作表“Sheet2”里，工作激活事件代碼： Private Sub Worksheet_Activate() Call checkDic With Me.ComboBox1 .List = dic.keys .Top = Range("D1").Top .Left = Range("D1").Left .Width = Range("D1").Width .Height = Range("D1").Height End WithEnd Sub2、在工作表“Sheet2”里，combobox1控件Change事件、工作表Change事件、打印按鈕點擊事件代碼： Private Sub ComboBox1_Change() Range("D1").Value = Me.ComboBox1.TextEnd Sub
Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer, j As Integer Dim class, weekday, num
If Target.Address = "$D$1" Then Call checkDic class = Target.Value For j = 3 To 9 weekday = Cells(3, j) For i = 6 To 15 num = Cells(i, 2) If dic.exists(class) Then If dic(class).exists(weekday) Then Cells(i, j).Value = dic(class)(weekday)(num) End If End If Next Next End IfEnd Sub
Private Sub CmdPrintAll_Click() Dim class Call checkDic If Not Application.Dialogs(xlDialogPrinterSetup).Show Then Exit Sub For Each class In dic.keys Range("D1").Value = class Me.PrintOut copies:=1 Next MsgBox "打印完成！"End Sub
3、在myModule里，定義公共字典變量dic，自定義過程getDic把數(shù)據(jù)裝入字典，checkDic過程，檢查字典是否有數(shù)據(jù)，沒有則調(diào)用getDic過程：Public dic As ObjectSub getDic() Dim i As Integer, j As Integer Dim arr() Dim ws As Worksheet Dim lRow As Integer Dim lCol As Integer Dim class, weekday, num Set ws = ThisWorkbook.Sheets("Sheet1") Set dic = CreateObject("Scripting.Dictionary") With ws lRow = .UsedRange.Rows.Count lCol = .UsedRange.Columns.Count arr = .Cells(1, 1).Resize(lRow, lCol).Value
End With '//循環(huán)數(shù)組 For i = 3 To lRow class = arr(i, 1) If class <> "" Then For j = 2 To lCol If arr(1, j) <> "" Then weekday = arr(1, j) End If num = arr(2, j) If Not dic.exists(class) Then Set dic(class) = CreateObject("Scripting.Dictionary") End If If Not dic(class).exists(weekday) Then Set dic(class)(weekday) = CreateObject("Scripting.Dictionary") End If dic(class)(weekday)(num) = arr(i, j)
Next End If NextEnd Sub
Sub checkDic() If dic Is Nothing Then Call getDic ElseIf dic.Count = 0 Then Call getDic End IfEnd Sub
|
