Formula cells not updating
The simple chart in this example is shown below its data range; the series is selected to highlight the data used to plot the series and to show the can only be a whole number between 1 and the number of series in the chart.
Or change the X values of all series from column A to column B?
When we try to change from column C to column D Excel chokes, because it can’t read the series formula we want it to change.
Excel can’t read the formula for a line or XY series with no data, but fortunately it can read other types, so we can do just fine if we change the series temporarily to a column chart, edit the series, and change the series back. Series Collection s Formula = "" On Error Resume Next s Formula = my Srs.
Here’s the code: Sub Change Series Formula All Charts() ''' Do all charts in sheet Dim o Chart As Chart Object Dim Old String As String, New String As String Dim my Srs As Series Dim i Chart Type As Xl Chart Type Dim s Formula As String Old String = Input Box("Enter the string to be replaced:", "Enter old string") If Len(Old String) 1 Then New String = Input Box("Enter the string to replace " & """" _ & Old String & """:", "Enter new string") For Each o Chart In Active Sheet. Formula On Error Go To 0 The intermediate chart still looks blank, but if we are attentive we might notice the horizontal axis has changed from the typical XY value-style axis to a typical column category-style axis.
After the series is changed, the columns reflect the new values.But if you have to do this for every series in your chart, and every chart on the worksheet, you will be editing in the formula bar all day.