ActiveChart.SetSourceData ¨Ó·½¸ê®Æ°ÝÃD (¤v¸Ñ¨M)

FAlonso

¾Þ§@­ì·N¬O¦bB2¿ï¾Ü¶}©l¤é´Á,B3¿ï¾Üµ²§ô¤é´Á,B4¿ï¾Ü¼Æ¾Ú¦C(¨Ò¦p³Ì°ª­È,³Ì§C­È)µ¥,¦A³z¹Lµ{¦¡Ã¸µe¸Ó¼Æ¾Ú¦Cªº¹Ïªí

¥H¤Uµ{¦¡¦¨¥\¹B¦æ

1. Sub plotgraph()
   
2. Dim myrange As Range, error As Boolean, x As Integer, y As Integer, mycolumn, mytitle
   
3. With Sheet1
   
4. .Activate
   
5. .[a1].Activate
   
6. End With
   
7. 
   
8. error = False
   
9. x = 0
   
10. y = 0
    
11. mytitle = [b4].Value
    
12. 
    
13. If [b2] = "" Or [b3] = "" Then
    
14. MsgBox "No date"
    
15. Exit Sub
    
16. End If
    
17. 
    
18. If DateValue([a8]) > DateValue([b2]) Then
    
19. MsgBox "Before starting period"
    
20. error = True
    
21. End If
    
22. 
    
23. If DateValue([b3]) > DateValue([a41]) Then
    
24. MsgBox "After ending period"
    
25. error = True
    
26. End If
    
27. 
    
28. If error = True Then
    
29. Exit Sub
    
30. End If
    
31. 
    
32. For Each myrange In Range([a8], [a65536].End(xlUp))
    
33. If DateValue(myrange) >= DateValue([b2]) Then
    
34. x = myrange.Row
    
35. Exit For
    
36. End If
    
37. Next
    
38. 
    
39. For Each myrange In Range([a8], [a65536].End(xlUp))
    
40. If DateValue(myrange) > DateValue([b3]) Then
    
41. y = myrange.Row - 1
    
42. Exit For
    
43. End If
    
44. Next
    
45. 
    
46. If mytitle = "¶}½L" Then
    
47.     mycolumn = "B"
    
48. ElseIf mytitle = "³Ì°ª" Then
    
49.     mycolumn = "C"
    
50. ElseIf mytitle = "³Ì§C" Then
    
51.     mycolumn = "D"
    
52. ElseIf mytitle = "¦¬½L" Then
    
53.     mycolumn = "E"
    
54. End If
    
55. 
    
56. Sheet1.ChartObjects(1).Select
    
57. ActiveChart.HasTitle = True
    
58. ActiveChart.ChartTitle.Text = mytitle
    
59. ActiveChart.HasLegend = False
    
60. ActiveChart.SetSourceData Source:=Sheet1.Range("A" & x & ":" & "A" & y & "," & mycolumn & x & ":" & mycolumn & y), PlotBy:=xlColumns
    
61. 
    
62. With Sheet1
    
63. .Activate
    
64. .[a1].Activate
    
65. End With
    
66. 
    
67. End Sub

½Æ»s¥N½X

FAlonso

½Ð°Ñ¦Ò¥H¤Wµ{¦¡

GBKEE

¦^´_ 2# FAlonso
[·|¥X²{«¬ºA¤£²Å¦XªºÄµ¸¹,¸Ó¦p¦óÁקK?]
§Aªº¿ù»~¦b©ó­ì¥»ªºµ{¦¡¦b°õ¦æ®É
ActiveSheet.Selection ¬O .ChartObjects(1) ¦Ó¤£¬O RANGE

­×§ï§Aªºµ{¦¡¦p¤U
b2 b3 ½Ðª½±µ¿é¤J¤é´Á §Y¥i

   

1. Sub plotgraph()
   
2. Dim myrange As Range, error As Boolean, x As Integer, y As Integer
   
3. Dim Rng As Range
   
4. error = False
   
5. x = 0
   
6. y = 0
   
7. With Sheet1
   
8.     .Activate
   
9.     .[a1].Activate   
   
10.      '[·|¥X²{«¬ºA¤£²Å¦XªºÄµ¸¹,¸Ó¦p¦óÁקK?]
    
11.     '§Aªº¿ù»~¦b©ó­ì¥»ªºµ{¦¡¦b°õ¦æ®É
    
12.     'ActiveSheet.Selection ¬O .ChartObjects(1) ¦Ó¤£¬O RANGE
    
13.     Set Rng = .Range(.[a8], .[a65536].End(xlUp))
    
14.     If .[b2] = "" Or .[b3] = "" Then
    
15.         MsgBox "No date"
    
16.         error = True
    
17.     ElseIf .[b2] > .[b3] Then
    
18.         MsgBox .[b2] & ">" & .[b3]
    
19.         error = True
    
20.     ElseIf Rng(1) > .[b2] Then
    
21.         MsgBox "Before starting period"
    
22.         error = True
    
23.     ElseIf .[b3] > Rng(Rng.Count) Then
    
24.         MsgBox "After ending period"
    
25.         error = True
    
26.     End If
    
27.     If error = True Then Exit Sub
    
28.     For Each myrange In Rng
    
29.         If myrange >= .[b2] Then
    
30.             x = myrange.Row - 1
    
31.             Exit For
    
32.         End If
    
33.     Next
    
34.     For Each myrange In Rng
    
35.         If .[b3] <= myrange Then
    
36.             y = myrange.Row - 1
    
37.             Exit For
    
38.         End If
    
39.     Next
    
40.     If Sheet1.ChartObjects.Count = 0 Then Charts.Add
    
41.     .ChartObjects(1).Select
    
42.     ActiveChart.ChartType = xlLineMarkers
    
43.     ActiveChart.Location xlLocationAsObject, Name:="Sheet1"
    
44.     ActiveChart.HasDataTable = False
    
45.     .Activate
    
46.     If [b4] = "¶}½L" Then
    
47.         ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",B" & x & ":B" & y), PlotBy:=xlColumns
    
48.     ElseIf .[b4] = "³Ì°ª" Then
    
49.         ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",C" & x & ":C" & y), PlotBy:=xlColumns
    
50.     ElseIf .[b4].Value = "³Ì§C" Then
    
51.         ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",D" & x & ":D" & y), PlotBy:=xlColumns
    
52.     ElseIf .[b4].Value = "¦¬½L" Then
    
53.         ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",E" & x & ":E" & y), PlotBy:=xlColumns
    
54.     End If
    
55. End With
    
56. End Sub

½Æ»s¥N½X