列范圍輸入為數字，因此可以更改

我有 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

標籤：擅长 vba

上一篇：從VBA中的Array中獲取值的反向索引

下一篇：返回列表