我試圖將資料源從一張作業表放到另一張作業表中的圖表中。我能夠實作資料收集，但最后，當我們設定源資料時，它拋出運行時錯誤 1004 - Method 'Range' of object'_Global' 失敗。
以下是我正在使用的代碼
Sub UBCharts()
Set Wb = ThisWorkbook
Set WsCharts = Wb.Sheets("Trend Charts")
Set UBMainChart = WsCharts.ChartObjects("UBMainChart")
Set UBMonthlyYTDSht = Wb.Worksheets("UM - Monthly & YTD Trend")
YearValue = WsCharts.Range("A1").Value
'LookupDate = CDate("" & "01/01/" & YearValue & "")
Xrows = UBMonthlyYTDSht.Range("A" & Rows.Count).End(xlUp).Row
MatchStartRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, 1, 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
MatchEndRow = Application.WorksheetFunction.Match(CLng(CDate(DateSerial(YearValue, Month(CLng((DateAdd("m", -1, Date)))), 1))), UBMonthlyYTDSht.Columns("A:A"), 0)
UBMainChart.Activate
'ActiveChart.SetSourceData Source:=Range("'UM - Monthly & YTD Trend'!$A$1:$L$1,'UM - Monthly & YTD Trend'!$A$14:$L$23")
UBMainChart.Chart.ChartArea.ClearContents
On Error Resume Next
UBMonthlyYTDSht.Activate
Set ChartRange = UBMonthlyYTDSht.Range(Cells(MatchStartRow, 1), Cells(MatchEndRow, Lcols))
Set ChartRngTitles = UBMonthlyYTDSht.Range(Cells(1, 1), Cells(1, Lcols))
On Error GoTo 0
UBMainChart.Activate
RngStr = """'" & UBMonthlyYTDSht.Name & "'!" & ChartRngTitles.Address & "," & "'" & UBMonthlyYTDSht.Name & "'!" & ChartRange.Address & """"
ActiveChart.SetSourceData Source:=Range(RngStr), PlotBy:=xlColumns 'I'm getting error here
'"'UM - Monthly & YTD Trend'!$A$1:$L$1,'UM - Monthly & YTD Trend'!$A$14:$L$23"
End Sub
感謝你的幫助！
uj5u.com熱心網友回復：
Union在這里使用可能更容易：
UBMainChart.SetSourceData Source:=Union(ChartRangeTitles, ChartRange), PlotBy:=xlColumns
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/354463.html