我有 25 個帶有餅圖的電子表格。我需要遍歷電子表格并更改餅圖從何處獲取資訊。但我無法讓代碼作業。我來自這個：
Sub ChangePieValues()
Dim sheetno As Integer
sheetno = 14
Sheets(sheetno).Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2)$BA$3:$BA$6"
ActiveChart.FullSeriesCollection(1).Values = "=Worksheets(2)$BB$3:$BB$6"
End Sub
我還沒有添加回圈，因為這是簡單的部分。
我的問題在以下行：
ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2)$BA$3:$BA$6"
我想我可以寫這樣的東西：
ActiveChart.FullSeriesCollection(1).XValues = "=Worksheets(2).((Range(55,3)):(Range(55,6))"
我正在嘗試將 XValues 的范圍更改為一個數字，以便在瀏覽不同的電子表格時更改該數字。
如何更改輸入列索引的方式，以便可以通過回圈進行更改。
uj5u.com熱心網友回復：
根據您的問題和示例代碼-您在其中向 Excel 提供一個參考范圍的字串-我建議以下解決方案：
ActiveChart.FullSeriesCollection(1).XValues = _
"'" & Worksheets(2).Name & "'!" & Cells(3, 53).Address & ":" & Cells(6, 53).Address
但是，更好的方法是為屬性提供Range物件（而不是參考 range 物件的字串）。這也應該有效，如下所示：
ActiveChart.FullSeriesCollection(1).XValues = _
Worksheets(2).Range(Worksheets(2).Cells(3, 53), Worksheets(2).Cells(6, 53))
為了使閱讀更整潔，我會使用：
With Worksheets(2)
ActiveChart.FullSeriesCollection(1).XValues = _
.Range(.Cells(3, 53), .Cells(6, 53))
End With
在您的示例嘗試中，您非常接近實作這一目標，但是當您將位置用雙引號括起來時 - Excel 將它們視為參考范圍的字串。
uj5u.com熱心網友回復：
VBA ChartObjects：更改源資料 ( SetSourceData)
- 在包含
ThisWorkbook此Chart 1代碼BA3:BA6（
Sub ChangeSourceData()
' Define constants.
' Source
Const sId As Variant = 2 ' name or index ('As Variant')
Const sFirstColumnAddress As String = "BA3:BA6"
' The number of source columns ('scCount') is equal
' to the number of destination worksheets.
Const scCount As Long = 25
' Destination
Const dFirstIndex As Long = 14 ' First Destination Worksheet
Const dChartName As String = "Chart 1" ' Each Worksheet's Chart Name
' Reference the workbook ('wb').
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
' Reference the source range ('srg').
Dim sws As Worksheet: Set sws = wb.Worksheets(sId)
Dim srg As Range: Set srg = sws.Range(sFirstColumnAddress).Resize(, scCount)
' Declare additional variables.
' Source
Dim sc As Long
' Destination
Dim dws As Worksheet
Dim dch As Chart
Dim dcho As ChartObject
Dim dIndex As Long
' Loop and apply.
' Loop through the columns ('sc') of the source range, indirectly...
For sc = 1 To scCount
' ... looping through the destination worksheets ('dIndex').
dIndex = dFirstIndex sc - 1
' Reference the destination worksheet ('dws') by index,...
Set dws = wb.Worksheets(dIndex)
' ... to reference its 'ChartObject' ('dcho') by name ...
Set dcho = dws.ChartObjects(dChartName)
' ... to reference its 'Chart' ('dch') avoiding 'Activate'.
Set dch = dcho.Chart
' Set the new (one-column) data range for the chart.
dch.SetSourceData srg.Columns(sc)
'dch.FullSeriesCollection(1).ApplyDataLabels
Next sc
' Inform.
MsgBox "Source data changed.", vbInformation
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/508288.html