標題: ActiveChart.SetSourceData 來源資料問題 (己解決) [打印本頁]

---

作者: FAlonso    時間: 2011-3-12 11:33     標題: ActiveChart.SetSourceData 來源資料問題 (己解決)

本帖最後由 FAlonso 於 2011-3-13 15:21 編輯

操作原意是在B2選擇開始日期,B3選擇結束日期,B4選擇數據列(例如最高值,最低值)等,再透過程式繪畫該數據列的圖表

以下程式成功運行

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 = "開盤" Then
    
47.     mycolumn = "B"
    
48. ElseIf mytitle = "最高" Then
    
49.     mycolumn = "C"
    
50. ElseIf mytitle = "最低" Then
    
51.     mycolumn = "D"
    
52. ElseIf mytitle = "收盤" 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

複製代碼

---

作者: FAlonso    時間: 2011-3-12 11:38

本帖最後由 FAlonso 於 2011-3-13 15:22 編輯

請參考以上程式

---

作者: GBKEE    時間: 2011-3-12 20:08

回復 2# FAlonso
[會出現型態不符合的警號,該如何避免?]
你的錯誤在於原本的程式在執行時
ActiveSheet.Selection 是 .ChartObjects(1) 而不是 RANGE

修改你的程式如下
b2 b3 請直接輸入日期 即可

   

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.      '[會出現型態不符合的警號,該如何避免?]
    
11.     '你的錯誤在於原本的程式在執行時
    
12.     'ActiveSheet.Selection 是 .ChartObjects(1) 而不是 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] = "開盤" 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 = "最低" Then
    
51.         ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",D" & x & ":D" & y), PlotBy:=xlColumns
    
52.     ElseIf .[b4].Value = "收盤" 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

複製代碼

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)