ActiveChart.SetSourceData 來源資料問題 (己解決)

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2011-3-12 11:33 | 顯示全部帖子

本帖最後由 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