Excel VBA：读取、更新和设置系列的 XV

Question

是否可以读取，然后更新，然后设置一系列XVALUE属性

步骤1)x = cht.FullSeriesCollection(1).XValues例如x = "=Dashboard!$S$137:$ x $137"，注意$ x

步骤2)修改X例如x = "=Dashboard!$S$137:$W$137"，注意$W

步骤3)cht.FullSeriesCollection(1)。XValues = x

Thanks in advance

Answer 1

下面的代码假设包含图表的工作簿是活动工作簿。如果不是这种情况，请确保它实际上是活动的工作簿。

Dim sr As Series
Set sr = cht.FullSeriesCollection(1)

Dim seriesFormula As String
seriesFormula = sr.Formula

Dim oldXValuesFormula As String
oldXValuesFormula = Split(seriesFormula, ",")(1)

With Range(oldXValuesFormula)
Dim newXValuesFormula As String
newXValuesFormula = .Resize(, .Columns.Count - 1).Address(external:=True)
End With

sr.XValues = Range(newXValuesFormula)