ActiveChart.SetSourceData 來源資料問題 (己解決)
- 帖子
- 135
- 主題
- 19
- 精華
- 0
- 積分
- 165
- 點名
- 0
- 作業系統
- XP
- 軟體版本
- 2003
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 香港
- 註冊時間
- 2010-12-9
- 最後登錄
- 2012-8-27
|
ActiveChart.SetSourceData 來源資料問題 (己解決)
本帖最後由 FAlonso 於 2011-3-13 15:21 編輯
操作原意是在B2選擇開始日期,B3選擇結束日期,B4選擇數據列(例如最高值,最低值)等,再透過程式繪畫該數據列的圖表
以下程式成功運行- Sub plotgraph()
- Dim myrange As Range, error As Boolean, x As Integer, y As Integer, mycolumn, mytitle
- With Sheet1
- .Activate
- .[a1].Activate
- End With
- error = False
- x = 0
- y = 0
- mytitle = [b4].Value
- If [b2] = "" Or [b3] = "" Then
- MsgBox "No date"
- Exit Sub
- End If
- If DateValue([a8]) > DateValue([b2]) Then
- MsgBox "Before starting period"
- error = True
- End If
- If DateValue([b3]) > DateValue([a41]) Then
- MsgBox "After ending period"
- error = True
- End If
- If error = True Then
- Exit Sub
- End If
- For Each myrange In Range([a8], [a65536].End(xlUp))
- If DateValue(myrange) >= DateValue([b2]) Then
- x = myrange.Row
- Exit For
- End If
- Next
- For Each myrange In Range([a8], [a65536].End(xlUp))
- If DateValue(myrange) > DateValue([b3]) Then
- y = myrange.Row - 1
- Exit For
- End If
- Next
- If mytitle = "開盤" Then
- mycolumn = "B"
- ElseIf mytitle = "最高" Then
- mycolumn = "C"
- ElseIf mytitle = "最低" Then
- mycolumn = "D"
- ElseIf mytitle = "收盤" Then
- mycolumn = "E"
- End If
- Sheet1.ChartObjects(1).Select
- ActiveChart.HasTitle = True
- ActiveChart.ChartTitle.Text = mytitle
- ActiveChart.HasLegend = False
- ActiveChart.SetSourceData Source:=Sheet1.Range("A" & x & ":" & "A" & y & "," & mycolumn & x & ":" & mycolumn & y), PlotBy:=xlColumns
- With Sheet1
- .Activate
- .[a1].Activate
- End With
- End Sub
|
|
|
80 字節以內
不支持自定義 Discuz! 代碼
|
|
|
|
|
- 帖子
- 5923
- 主題
- 13
- 精華
- 1
- 積分
- 5986
- 點名
- 0
- 作業系統
- win10
- 軟體版本
- Office 2010
- 閱讀權限
- 150
- 性別
- 男
- 來自
- 台灣基隆
- 註冊時間
- 2010-5-1
- 最後登錄
- 2022-1-23
        
|
3#
發表於 2011-3-12 20:08
| 只看該作者
回復 2# FAlonso
[會出現型態不符合的警號,該如何避免?]
你的錯誤在於原本的程式在執行時
ActiveSheet.Selection 是 .ChartObjects(1) 而不是 RANGE
修改你的程式如下
b2 b3 請直接輸入日期 即可
- Sub plotgraph()
- Dim myrange As Range, error As Boolean, x As Integer, y As Integer
- Dim Rng As Range
- error = False
- x = 0
- y = 0
- With Sheet1
- .Activate
- .[a1].Activate
- '[會出現型態不符合的警號,該如何避免?]
- '你的錯誤在於原本的程式在執行時
- 'ActiveSheet.Selection 是 .ChartObjects(1) 而不是 RANGE
- Set Rng = .Range(.[a8], .[a65536].End(xlUp))
- If .[b2] = "" Or .[b3] = "" Then
- MsgBox "No date"
- error = True
- ElseIf .[b2] > .[b3] Then
- MsgBox .[b2] & ">" & .[b3]
- error = True
- ElseIf Rng(1) > .[b2] Then
- MsgBox "Before starting period"
- error = True
- ElseIf .[b3] > Rng(Rng.Count) Then
- MsgBox "After ending period"
- error = True
- End If
- If error = True Then Exit Sub
- For Each myrange In Rng
- If myrange >= .[b2] Then
- x = myrange.Row - 1
- Exit For
- End If
- Next
- For Each myrange In Rng
- If .[b3] <= myrange Then
- y = myrange.Row - 1
- Exit For
- End If
- Next
- If Sheet1.ChartObjects.Count = 0 Then Charts.Add
- .ChartObjects(1).Select
- ActiveChart.ChartType = xlLineMarkers
- ActiveChart.Location xlLocationAsObject, Name:="Sheet1"
- ActiveChart.HasDataTable = False
- .Activate
- If [b4] = "開盤" Then
- ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",B" & x & ":B" & y), PlotBy:=xlColumns
- ElseIf .[b4] = "最高" Then
- ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",C" & x & ":C" & y), PlotBy:=xlColumns
- ElseIf .[b4].Value = "最低" Then
- ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",D" & x & ":D" & y), PlotBy:=xlColumns
- ElseIf .[b4].Value = "收盤" Then
- ActiveChart.SetSourceData Source:=.Range("A" & x & ":A" & y & ",E" & x & ":E" & y), PlotBy:=xlColumns
- End If
- End With
- End Sub
|
|
|
|
|
|
|
|
- 帖子
- 135
- 主題
- 19
- 精華
- 0
- 積分
- 165
- 點名
- 0
- 作業系統
- XP
- 軟體版本
- 2003
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 香港
- 註冊時間
- 2010-12-9
- 最後登錄
- 2012-8-27
|
2#
發表於 2011-3-12 11:38
| 只看該作者
本帖最後由 FAlonso 於 2011-3-13 15:22 編輯
請參考以上程式 |
|
|
80 字節以內
不支持自定義 Discuz! 代碼
|
|
|
|
|
