register313

¦^´_ 1# dechiuan999

1. Private Sub CommandButton1_Click()
   
2. Dim mRng As Range
   
3.     Dim mRng1 As Range
   
4.     Dim mRng2 As Range
   
5.     Dim mRng3 As Range
   
6.     Dim mTotal%
   
7.     Dim oldMonth
   
8.     Dim mSht As Worksheet
   
9.     Dim mRow As Integer
   
10.         
    
11.     Set mSht = Worksheets(1)
    
12.     With mSht
    
13.         mRow = .Range("a1").End(xlDown).Row
    
14.         Set mRng = .Range("g1:l" & mRow)
    
15.         Set mRng1 = .Range("a2:a12")
    
16.         Set mRng2 = .Range("f2:f12")
    
17.         mTotal = Application.WorksheetFunction.Max(mRng2)
    
18.         Set mRng3 = Union(mRng1, mRng2)
    
19.     End With
    
20.    
    
21.     oldMonth = Month(Date) - 1
    
22.     Application.ScreenUpdating = False
    
23.     '·s¼W¹Ïªí¤§X¦ì¸m Y¦ì¸m ¼e °ª
    
24.     Set mychart = Sheet1.ChartObjects.Add(350, 0, 400, 200)
    
25.    
    
26.     Select Case mTotal
    
27.     Case 1 To 100
    
28.         mTotal = "100"
    
29.     Case 101 To 200
    
30.         mTotal = "200"
    
31.     Case 201 To 300
    
32.         mTotal = "300"
    
33.     Case 301 To 400
    
34.         mTotal = "400"
    
35.     Case 401 To 500
    
36.          mTotal = "500"
    
37.     Case esle
    
38.         mTotal = mTotal
    
39.     End Select
    
40.    
    
41.     With mychart.Chart
    
42.         .SetSourceData Source:=mRng3, PlotBy:=xlColumns
    
43.         .HasTitle = True
    
44.         .ChartType = xlColumnClustered
    
45.         .HasLegend = False
    
46.         .ApplyDataLabels xlDataLabelsShowValue
    
47.         '.Axes(xlCategory).TickLabels.Orientation = xlHorizontal
    
48.         .ChartTitle.Characters.Text = oldMonth & " ¤ë¥÷²Î­pªí"
    
49.         .ChartTitle.Font.Bold = False
    
50.         .ChartTitle.Font.Size = 12
    
51.         .PlotArea.Top = 16
    
52.         .PlotArea.Height = 160
    
53.         .Axes(xlValue).MaximumScale = mTotal
    
54.         .Axes(xlCategory, xlPrimary).HasTitle = False
    
55.         .Axes(xlValue, xlPrimary).HasTitle = False
    
56.         .ChartArea.Font.Size = 8
    
57.         .ChartTitle.Font.Size = 10
    
58.      End With
    
59.    
    
60.     Application.ScreenUpdating = True
    
61. 
    
62. End Sub

½Æ»s¥N½X

TEST¤G.rar (13.05 KB)

¤U¸ü¦¸¼Æ: 8

2012-2-28 17:51

register313

¦^´_ 4# dechiuan999


Set Rng = Range("H1:L12")   
«hRng(1)=Range("H1")
   Rng(2)=Range("I1")
   Rng(6)=Range("H2")
...¥ý¾î¦Aª½ ¤§±Æ¦C¤è¦¡
¬°Àx¦s®æ¤§¥t¤@ºØªí¥Üªk

¤U¸ü (95.68 KB)

2012-2-28 23:08