Ecel收支理財(cái)管理及財(cái)務(wù)知識(shí)分析系統(tǒng)

1、Excel收支治理系統(tǒng)程序功能：銀行信息記錄存款利息計(jì)算銀行總資金匯總銀行年收益計(jì)算收支記錄收入項(xiàng)目記錄，增加到銀行賬戶支出項(xiàng)目記錄，選擇支出賬戶可對(duì)每條記錄進(jìn)行修改，并與賬戶關(guān)聯(lián)收支項(xiàng)目治理，可增加或刪除收支項(xiàng)目本程序操作靈活，界面人性化，比如刪除“銀行記錄”金額，可將本條記錄信息全部刪除（需要確認(rèn)）；收支記錄中信息輸入完整，自動(dòng)與銀行賬戶信息關(guān)聯(lián)；可自己添加銀行并修改利率。使用本程序可快速判不存款方式對(duì)收益的阻礙，比如5萬(wàn)存入工商銀行：整存整取兩年，利息4400整存整取一年，利息3561（兩年后取）現(xiàn)在銀行利率也有差不，存不同銀行收益相差多少也能方便了解。界面“銀行記錄”“銀行記錄”中復(fù)制

2、代碼如下：Private Sub Calendar1_Click()ActiveCell = Calendar1Calendar1.Visible = FalseEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim lvDim zhuancun(1 To 100)Dim lv_huo(1 To 1000)Dim lv_ding1_3(1 To 1000)Dim lv_ding1_6(1 To 1000)Dim lv_ding1_12(1 To 1000)Dim lv_ding1_24(1 To 1000)D

3、im lv_ding1_36(1 To 1000)Dim lv_ding1_60(1 To 1000)Dim lv_ding2_12(1 To 1000)Dim lv_ding2_36(1 To 1000)Dim lv_ding2_60(1 To 1000)Dim rng As Rangern = Range(b65536).End(xlUp).Row 最大行號(hào)cn = Range(b2).End(xlToRight).Column 最大列號(hào)Application.ScreenUpdating = False數(shù)據(jù)初始化If Sheet1.Cells(ActiveCell.Row, 1) = A

4、nd Sheet1.Cells(ActiveCell.Row, 3) = And Sheet1.Cells(ActiveCell.Row, 2) Then Sheet1.Cells(ActiveCell.Row, 1) = 中國(guó)銀行End IfFor y = 3 To rn If Sheet1.Cells(y, 1) Then Sheet4.Select 查找銀行名稱 Set rng = Sheet4.B:B.Find(Sheet1.Cells(y, 1) 定位銀行 If Not rng Is Nothing Then rng.Font.ColorIndex = 3 顏色暫不設(shè)置 Applic

5、ation.Goto Reference:=rng.Address(, , xlR1C1) End If End If lv_huo(y) = Sheet4.Cells(ActiveCell.Row + 3, ActiveCell.Column + 1) lv_ding1_3(y) = Sheet4.Cells(ActiveCell.Row + 6, ActiveCell.Column + 1) lv_ding1_6(y) = Sheet4.Cells(ActiveCell.Row + 7, ActiveCell.Column + 1) lv_ding1_12(y) = Sheet4.Cell

6、s(ActiveCell.Row + 8, ActiveCell.Column + 1) lv_ding1_24(y) = Sheet4.Cells(ActiveCell.Row + 9, ActiveCell.Column + 1) lv_ding1_36(y) = Sheet4.Cells(ActiveCell.Row + 10, ActiveCell.Column + 1) lv_ding1_60(y) = Sheet4.Cells(ActiveCell.Row + 11, ActiveCell.Column + 1) lv_ding2_12(y) = Sheet4.Cells(Acti

7、veCell.Row + 13, ActiveCell.Column + 1) lv_ding2_36(y) = Sheet4.Cells(ActiveCell.Row + 14, ActiveCell.Column + 1) lv_ding2_60(y) = Sheet4.Cells(ActiveCell.Row + 15, ActiveCell.Column + 1) 返回sheet“銀行項(xiàng)目” Sheet1.SelectNext-格式初始化With Range(Sheet1.Cells(3, 1), Sheet1.Cells(rn + 30, cn).Interior .Pattern

8、= xlNone .TintAndShade = 0 .PatternTintAndShade = 0End With-取消列表 With Sheet1.Range(A:A).Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEM

9、ode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With With Sheet1.Range(C:C).Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage =

10、.IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 銀行列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E5:E & rn4 & With Range(A3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCel

11、lDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With項(xiàng)目列表 With Range(C3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=活期,整存整取,整存零取,零存整取,存

12、本取息,定活兩便 .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End WithFor Z = 3 To rn 銀行列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E5:E & rn4 & With Range(A & Z + 1).Vali

13、dation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 項(xiàng)目列表 With Rang

14、e(C & Z + 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=活期,整存整取,整存零取,零存整取,存本取息,定活兩便 .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = Tru

15、e .ShowError = True End With - 年利率 If Sheet1.Cells(Z, 3) = Huo Or Sheet1.Cells(Z, 3) = 活期 Then lv = lv_huo(Z) Sheet1.Cells(Z, 6) = lv End If If Sheet1.Cells(Z, 3) = ZZ Or Sheet1.Cells(Z, 3) = 整存整取 Then If Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells

16、(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_ding1_60(Z) 5年 End If Sheet1.Cells(Z, 6) = lv End If If Sheet1.Cells(Z, 3) = ZL LZ BX Or Sheet1.Cells(Z, 3) = 零存整取 Or Sheet1.Cells(Z, 3) = 整存零取 Or Sheet1.Cells(Z, 3) = 存本取息 Then If Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cel

17、ls(Z, 5) = 60 Then lv = lv_ding2_60(Z) 5年 End If Sheet1.Cells(Z, 6) = lv End If If Sheet1.Cells(Z, 3) = 定活兩便 Then If Sheet1.Cells(Z, 4) = And Sheet1.Cells(Z, 5) = Then dh = MsgBox(未區(qū)分各家銀行計(jì)算方法，結(jié)果不一定準(zhǔn)確，按利率60%計(jì)算？, vbYesNo, 提示) If dh = vbYes Then If Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And

18、 Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Then lv = lv_ding1_60(Z) 5年 End If lv = lv * 0.6 Else Sheet1.Cells(Z, 3) = End If Else If Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5)

19、 = 60 Then lv = lv_ding1_60(Z) 5年 End If lv = lv * 0.6 End If Sheet1.Cells(Z, 6) = lv End If - On Error Resume Next If Target.Column = 4 And Target.Value 存入日期 And Target.Row 2 And Target.Row = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1)

20、= Day(Sheet1.Cells(Z, 4) Then m = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 + Month(Sheet1.Cells(1, 1) - Month(Sheet1.Cells(Z, 4) End If If Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) = Year(Sheet1

21、.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4) Then m = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4) End If If Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Day(Sheet1.Cells(Z, 4)

22、Then m = (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4) - 1 End If If Year(Sheet1.Cells(1, 1) Year(Sheet1.Cells(Z, 4) Then m = 0 End If If Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) Month(Sheet1.Cells(

23、Z, 4) Then m = 0 End If If Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) Day(Sheet1.Cells(Z, 4) Then m = 0 End If - zhuan = Int(m / Sheet1.Cells(Z, 5) - 1 轉(zhuǎn)存次數(shù) If zhuan 2 And Sheet1.Cells(Z, 6) Then Sheet1.Cells(Z

24、, 7) = daoqi -到期總數(shù) If Sheet1.Cells(Z, 4) Then Sheet1.Cells(Z, 8).FormulaR1C1 = _ =DATE(YEAR(RC-4),MONTH(RC-4)+RC-3,DAY(RC-4) -到期日期 End If End If If Sheet1.Cells(Z, 7) Then Sheet1.Cells(Z, 9) = (Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2) -利息 Else Sheet1.Cells(Z, 9) = End If Sheet1.Cells(Z, 10) = zhuan -

25、轉(zhuǎn)存次數(shù) zhuancun(1) = Sheet1.Cells(Z, 7) + Sheet1.Cells(Z, 7) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui) 第一次轉(zhuǎn)存到期總數(shù) If zhuan = 2 Then For i = 2 To zhuan zhuancun(i) = zhuancun(i - 1) + zhuancun(i - 1) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui) Next End If If Int(m / Sheet1.

26、Cells(Z, 5) = m / Sheet1.Cells(Z, 5) Then Sheet1.Cells(Z, 11).FormulaR1C1 = _ =DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7) -轉(zhuǎn)存到期日期 Else Sheet1.Cells(Z, 11).FormulaR1C1 = _ =DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7) -轉(zhuǎn)存到期日期 End If If zhuan 1 Then Sheet1.Cells(Z, 10) = Sheet1.Cells(Z

27、, 11) = Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7) Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9) Else Sheet1.Cells(Z, 12) = zhuancun(zhuan) -當(dāng)前總額 Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 12) - Sheet1.Cells(Z, 2) -當(dāng)前利息 End If - 格式 If Z Mod 2 = 1 Then 當(dāng)前行數(shù)除以2的余數(shù)為1（奇數(shù)） With Range(Sheet1.Cells(Z, 1), Sheet1.Cells

28、(Z, 14).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10198015 .TintAndShade = 0 .PatternTintAndShade = 0 End With ElseIf Z Mod 2 = 0 Then 當(dāng)前行數(shù)除以2的余數(shù)為0（偶數(shù)） With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color =

29、 13421823 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If - 未選擇銀行不計(jì)利率 If Sheet1.Cells(Z, 1) = Then lv = 0 Sheet1.Cells(Z, 6) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 9) = End If 活期不考慮到期時(shí)刻，按存入時(shí)刻到當(dāng)前日期利息計(jì)算 If Sheet1.Cells(Z, 3) = 活期 Then tian = Date - Sheet1.Cells(Z, 4) nian1 = Int(tian / 365)

30、 年數(shù) yue1 = Sheet1.Cells(Z, 5) - 12 * nian 月數(shù) Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * lv / 100 * (tian / 365) Sheet1.Cells(Z, 9) = Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 5) = Sheet1.Cells(Z, 6) = lv Sheet1.Cells(Z, 8) = Sheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11)

31、 = Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7) Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9) End If 未選擇存入時(shí)刻 If Sheet1.Cells(Z, 4) = Then Sheet1.Cells(Z, 9) = Sheet1.Cells(Z, 10) = Sheet1.Cells(Z, 11) = Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 2) End

32、 If 到期提示 If Sheet1.Cells(Z, 8) Sheet1.Cells(1, 1) Then With Sheet1.Cells(Z, 8).Font .Color = -65281 .TintAndShade = 0 End With Else With Sheet1.Cells(Z, 8).Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 End With End If If Sheet1.Cells(Z, 11) Sheet1.Cells(1, 1) Then With Sheet1.Cells(Z, 11).

33、Font .Color = -65281 .TintAndShade = 0 End With Else With Sheet1.Cells(Z, 11).Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 End With End IfNext-For j = 3 To rn + 1000 If Sheet1.Cells(j, 2) = And Sheet1.Cells(j, 3) Then sc = MsgBox(確認(rèn)刪除該項(xiàng)目？, vbYesNo, 提示) If sc = vbYes Then Sheet1.Cells(j, 1

34、) = Sheet1.Cells(j, 3) = Sheet1.Cells(j, 4) = Sheet1.Cells(j, 5) = Sheet1.Cells(j, 6) = Sheet1.Cells(j, 7) = Sheet1.Cells(j, 8) = Sheet1.Cells(j, 9) = Sheet1.Cells(j, 10) = Sheet1.Cells(j, 11) = Sheet1.Cells(j, 12) = Sheet1.Cells(j, 13) = Sheet1.Cells(j, 14) = End If Rows(j).Delete End IfNext圖表數(shù)據(jù)處理圖

35、表1，餅圖，顯示各銀行資金分布Sheet6.Columns(A:B).ClearSheet6.Cells(1, 1) = 圖表1zong = 0ben = 0nianli = 0For K = 3 To rn rn1 = Sheet6.Range(a65536).End(xlUp).Row If rn1 = 2 Then ActiveSheet.ChartObjects(圖表 1).Activate ActiveChart.SetSourceData Source:=Sheet6.Range(A2:A & rn1 & , B2:B & rn1 & ) Sheet1.Cells(r, c).Se

36、lect 設(shè)置繪圖區(qū)格式 With Sheets(1).ChartObjects(圖表 1) .Name .Left .Top .Height = 250 .Width .Chart.ChartType End WithEnd IfWith Range(O3, O4) .HorizontalAlignment = xlRight .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrde

37、r = xlContext .MergeCells = False End With With Range(O3).Font .Color = -16751104 .TintAndShade = 0 End With With Range(O4).Font .Color = -3368704 .TintAndShade = 0 End With Range(O3, O4).Font.Size = 9End Sub界面“收支記錄”“收支記錄”中復(fù)制代碼如下：Dim riDim r(0 To 1)Dim r1(0 To 1)Dim r2(0 To 1)Dim r3(0 To 1)Dim r10(0

38、 To 1)Dim r11(0 To 1)Dim r12(0 To 1)Dim r13(0 To 1)Dim kongDim kong1Dim kong2Dim kong10Dim kong11Dim kong12Dim flag_shanDim flag_shan10Private Sub Calendar1_Click()ActiveCell = Calendar1Calendar1.Visible = FalseEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)ri = ri + 1 ri定義為鼠標(biāo)點(diǎn)擊次

39、數(shù)rik = ri Mod 2 rik為0或1rns = Range(B65536).End(xlUp).Row 收入最大行號(hào)rnz = Range(L65536).End(xlUp).Row 支出最大行號(hào)If rns = rnz Then rn = rnsElse rn = rnzEnd Ifcns = Range(B2).End(xlToRight).Column 最大列號(hào)cnz = Range(L2).End(xlToRight).Column 最大列號(hào)格式初始化With Range(Sheet2.Cells(3, 1), Sheet2.Cells(rn + 10, cnz).Inter

40、ior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0End With-With Sheet2.Range(A:N).Validation .Delete .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage

41、 = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 收入項(xiàng)目列表更新 rn7s = Sheet7.Range(A65536).End(xlUp).Row f = =收支項(xiàng)目!A2:A & rn7s & With Range(B3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True

42、.InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 支出項(xiàng)目列表更新 rn7z = Sheet7.Range(C65536).End(xlUp).Row f = =收支項(xiàng)目!C2:C & rn7z & With Range(L3).Validation .Delete .Add Type:=xlValidateList, AlertS

43、tyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 收入賬戶列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E

44、5:E & rn4 & With Range(D3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError

45、 = True End With 支出賬戶列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E5:E & rn4 & With Range(n3).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessa

46、ge = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End WithFor Z = 2 To rn 收入項(xiàng)目列表更新 rn7s = Sheet7.Range(A65536).End(xlUp).Row f = =收支項(xiàng)目!A2:A & rn7s & With Range(B & Z + 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlB

47、etween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 支出項(xiàng)目列表更新 rn7z = Sheet7.Range(C65536).End(xlUp).Row f = =收支項(xiàng)目!C2:C & rn7z & With Range(L & Z + 1).Valid

48、ation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 收入賬戶列表更新 rn4 =

49、Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E5:E & rn4 & With Range(D & Z + 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IME

50、Mode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With 支出賬戶列表更新 rn4 = Sheet4.Range(e65536).End(xlUp).Row f = =差不多信息!E5:E & rn4 & With Range(N & Z + 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f .IgnoreBlank = True .

51、InCellDropdown = True .InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl .ShowInput = True .ShowError = True End With On Error Resume Next If Target.Column = 1 And Target.Value 日期 And Target.Row 2 And Target.Row = rn + 1 Then Calendar1.Visible = True 日歷控件 Calen

52、dar1.Left = Cells(Target.Row, 1).Left Calendar1.Top = Cells(Target.Row + 1, 1).Top With Calendar1 當(dāng)前日期 .Year = Year(Now) .Month = Month(Now) .Day = Day(Now) End With ElseIf Target.Column = 11 And Target.Value 日期 And Target.Row 2 And Target.Row = rn + 1 Then Calendar1.Visible = True 日歷控件 Calendar1.Le

53、ft = Cells(Target.Row, 11).Left Calendar1.Top = Cells(Target.Row + 1, 11).Top With Calendar1 當(dāng)前日期 .Year = Year(Now) .Month = Month(Now) .Day = Day(Now) End With Else Calendar1.Visible = False End If 格式 If Z Mod 2 = 1 Then 當(dāng)前行數(shù)除以2的余數(shù)為1（奇數(shù)） If Sheet2.Cells(Z + 1, 1) Or Sheet2.Cells(Z + 1, 2) Then With

54、 Range(Sheet2.Cells(Z + 1, 1), Sheet2.Cells(Z + 1, cns).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If If Sheet2.Cells(Z + 1, 11) Or Sheet2.Cells(Z + 1, 12) Then With Range(Sheet2.Cells(Z + 1, 11), Sheet2.Cell

55、s(Z + 1, cnz).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 13421823 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If ElseIf Z Mod 2 = 0 Then 當(dāng)前行數(shù)除以2的余數(shù)為0（偶數(shù)） If Sheet2.Cells(Z + 1, 1) Or Sheet2.Cells(Z + 1, 2) Then With Range(Sheet2.Cells(Z + 1, 1), Sheet2.Cells(Z

56、+ 1, cns).Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 10198015 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If If Sheet2.Cells(Z + 1, 11) Or Sheet2.Cells(Z + 1, 12) Then With Range(Sheet2.Cells(Z + 1, 11), Sheet2.Cells(Z + 1, cnz).Interior .Pattern = xlSolid .Pat

57、ternColorIndex = xlAutomatic .Color = 10198015 .TintAndShade = 0 .PatternTintAndShade = 0 End With End If End If -Next-收入-If rik = 0 Then r2(0) = Range(b65536).End(xlUp).Row 某次點(diǎn)擊收入項(xiàng)目最大行號(hào)End IfIf rik = 1 Then r2(1) = Range(b65536).End(xlUp).Row 下一次點(diǎn)擊收入項(xiàng)目最大行號(hào)End If增加行、減少行推斷，用于記錄被刪除行信息If rik = 0 Then I

58、f r2(0) r2(1) Then zeng2 = 增加行 End If If r2(0) r2(0) Then zeng2 = 增加行 End If If r2(1) r2(0) Then zeng2 = 減少行 End IfEnd IfIf zeng2 = 減少行 Then flag_shan = 可刪除End If刪除收入記錄For j = 3 To rn + 1000 記錄被刪除項(xiàng)目信息 If Sheet2.Cells(j, 1) And Sheet2.Cells(j, 2) = And Sheet2.Cells(j, 3) And Sheet2.Cells(j, 4) Then I

59、f flag_shan = 可刪除 Then s2 = Sheet2.Cells(j, 2) s3 = Sheet2.Cells(j, 3) s4 = Sheet2.Cells(j, 4) End If End If If Sheet2.Cells(j, 2) = And Sheet2.Cells(j, 3) & Sheet2.Cells(j, 4) Then sc = MsgBox(確認(rèn)刪除該項(xiàng)目？, vbYesNo, 提示) If sc = vbYes Then Sheet2.Cells(j, 1) = Sheet2.Cells(j, 3) = Sheet2.Cells(j, 4) = 信

60、息欄 Sheet2.Cells(1, 1) = s2 & & s3 & 元收入記錄已刪除， & & s4 & 存款金額未發(fā)生變化。 flag_shan = 已刪除 End If End If Next-收入增加到銀行記錄中，為活期-If rik = 0 Then r(0) = Range(d65536).End(xlUp).Row 某次點(diǎn)擊賬戶最大行號(hào)End IfIf rik = 1 Then r(1) = Range(d65536).End(xlUp).Row 下一次點(diǎn)擊賬戶最大行號(hào)End If增加行、減少行推斷，用于增加銀行記錄If rik = 0 Then If r(0) r(1) Th