I've found a VBA code here in SO and have adjusted it. This is the code:

```
Sub UpdateChart()
'Adjusted version of Søren Holten Hansen sub found in SO
Const strChartName As String = "Chart 6"
Dim oChart As Chart
Set oChart = ThisWorkbook.Worksheets(2).ChartObjects(strChartName).Chart
Dim colSeries As SeriesCollection
Set colSeries = oChart.SeriesCollection
Dim i As Long, r As Long, n As Long 'counters
Dim p1 As Long, p2 As Long, p3 As Long '~positions of commas in Formula
Dim rngY As Range 'Y values in Formula (3rd SERIES formula argument)
Dim ax As Range 'Axis (X values) in Formula (2nd SERIES formula argument)
Dim rngLast As Range
Dim strYAddress As String
'Cycles through each Series
For n = 1 To colSeries.Count Step 1
r = 0
'Finds the current range of the series (3rd SERIES argument) and
' axis range (2nd argument) - ~positions of commas in Formula
For i = 1 To Len(colSeries(n).Formula) Step 1
If Mid(colSeries(n).Formula, i, 1) = "," Then
r = r + 1
If r = 1 Then p1 = i + 1
If r = 2 Then p2 = i
If r = 3 Then p3 = i
End If
Next 'i
'Updates axis (2nd argument in Formula (SERIES formula))
Set ax = Range(Mid(colSeries(n).Formula, p1, p2 - p1))
Set rngLast = ax.End(xlDown)
Set ax = Range(ax, rngLast)
colSeries(n).XValues = ax
'Updates Y values (3rd argument in Formula (SERIES formula))
strYAddress = Mid(colSeries(n).Formula, p2 + 1, p3 - p2 - 1)
Set rngY = ThisWorkbook.Worksheets(2).Range(strYAddress) '<<<<<
Set rngY = Range(rngY, Cells(rngLast.Row, rngY.Column))
colSeries(n).Values = rngY
Set rngLast = Nothing
Set ax = Nothing
Set rngY = Nothing
Next 'n
Set rngLast = Nothing
Set ax = Nothing
Set rngY = Nothing
Set colSeries = Nothing
Set oChart = Nothing
End Sub
```

Link to the original Sub: by Søren Holten Hansen

I understand VBA quite moderately, but I have no idea how to fix this thing and why is it occurring. I have marked the line which causes the error ("<<<<<").

At first I had just this one line: `Set rngY = Range(Mid(colSeries(n).Formula, p2 + 1, p3 - p2 - 1))`

. Then I had this error: "Run-time error '1004' : Method 'Range' of object'_Global' failed". I have added the "qualified" reference and put the result of `Mid`

function to another variable (`strYAddress`

). But I am sure that the problem was not here.

Now when the Sub stops on the marked line, and I debug the sub, I see that `strYAddress`

has the value of `",2"`

which is wrong! If I go back and run the line `strYAddress = Mid(colSeries(n).Formula, p2 + 1, p3 - p2 - 1)`

again the value of `strYAddress`

becomes `"InvIst!$E$71:$E$88"`

which is correct(!) and I can proceed!

Where's the point? Why doesn't `Mid`

evaluate to the correct value from the first try??

Edit:

I still have no idea what was wrong with `Mid`

function. It seems that `colSeries(n).XValues = ax`

was preventing `Mid`

to evaluate to correct value, because I have noticed that **if this line is commented out, the result of Mid is correct...**

However, I decided to use `Split`

instead. Everything worked like a charm, and the code looks much better.

```
Sub UpdateChart()
Const strChartName As String = "Chart 6"
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(2)
Dim colSeries As SeriesCollection
Set colSeries = ws.ChartObjects(strChartName).Chart.SeriesCollection
Dim n As Long 'counter
Dim rngY As Range 'Y values in Formula (3rd SERIES formula argument)
Dim ax As Range 'Axis (X values) in Formula (2nd SERIES formula argument)
Dim strA() As String
Dim rngLast As Range
'Cycles through each Series
For n = 1 To colSeries.Count Step 1
'Separates 2 most significant arguments - axis and Y values
strA = Split(colSeries(n).Formula, ",")
'Updates axis (2nd argument in Formula (SERIES formula))
Set ax = ws.Range(strA(1))
Set rngLast = ax.Offset(-1).End(xlDown)
Set ax = Range(ax, rngLast)
colSeries(n).XValues = ax
'Updates Y values (3rd argument in Formula (SERIES formula))
Set rngY = ws.Range(strA(2))
Set rngY = Range(rngY, Cells(rngLast.Row, rngY.Column))
colSeries(n).Values = rngY
Next 'n
Set rngLast = Nothing
Set ax = Nothing
Set rngY = Nothing
Set colSeries = Nothing
Set ws = Nothing
End Sub
```