VBA replace Values and Xvalues in charts series

[jcosta92]

Hello,

I need a code to read and replace the values and xvalues of a chart series, specifically the row numbers. Can someone help? By now, the code I have doesn't get the values and xvalues as msgbox is a blank.

Sub test()
Dim cht As ChartObject
Dim srs As Series
Dim sht As Worksheet
Dim rng As String
Dim rngx As String
Dim i As Long
Dim j As Long




Set sht = ThisWorkbook.ActiveSheet


For Each cht In sht.ChartObjects
    cht.Activate
    
    For i = 1 To ActiveChart.SeriesCollection.Count
    With ActiveChart.SeriesCollection(i)
    .XValues = rngx
    .Values = rng
    
    MsgBox rngx 'returns a blank
    
    'after this fixed I need to get the first and last rows of Xvalues and Values and be able to replace them maybe with the replace function!!!



    End With
    Next i
Next cht


End Sub

Thank you

 

[duggie33]

Not sure if the code will work because I have never done this with charts but it seems you might want to change "rngx = Activechart.Seriescollection(i)". Likewise for rng.

 

[jcosta92]

Still with

rngx=XValues

it gives me a blank...it should give me something like "=Sheet1!'A2:A:10". What I need it to get, for example, the values of the rows (in this case 2 and 10) and replace them with my own values...something like:

frow= "first row of the range of Values"
lrow="last row of range of Values"
srs.Formula = WorksheetFunction.Substitute(srs.Formula, frow, i)
srs.Formula = WorksheetFunction.Substitute(srs.Formula, lrow, j)

 

[duggie33]

I don't think the "With ActiveChart.SeriesCollection(I)" sees the .XLValues in your new code. Get rid of the With…and EndWith and try “rngx = Activechart.Seriescollection(i).XValues” and “rng= Activechart.Seriescollection(i).Values”. I have not tried any of this so I do not know that it is going to work…there might be other syntax errors.


Consider creating dynamic named ranges and using those for your chart series. Relatively easy to adjust ranges that way.

 

[jcosta92]

I don't think the "With ActiveChart.SeriesCollection(I)" sees the .XLValues in your new code. Get rid of the With…and EndWith and try “rngx = Activechart.Seriescollection(i).XValues” and “rng= Activechart.Seriescollection(i).Values”. I have not tried any of this so I do not know that it is going to work…there might be other syntax errors.


Consider creating dynamic named ranges and using those for your chart series. Relatively easy to adjust ranges that way.

Thank you for your help.

It still gives me an error thought.

 

[duggie33]

This is the only way that I could find half of the information you are looking for with VBA.

Sub test()


    Dim cht As ChartObject
    Dim srs As Series
    Dim wb As Workbook
    Dim sht As Worksheet
    Dim rng As String
    Dim rngx As String
    Dim i As Long
    Dim j As Long


    Set wb = ActiveWorkbook
    Set sht = wb.ActiveSheet


    For Each cht In sht.ChartObjects


        For i = 1 To cht.Chart.SeriesCollection.Count


                rng = cht.Chart.SeriesCollection(i).Formula
                MsgBox rng


        Next i
        
    Next cht


End Sub

The syntax for charts is not intuitive at all for someone like me who is self-taught/learning. It took me about an hour to figure this much out. From my experience, using dynamic named ranges for controlling chart data is the way to go. I had a spreadsheet with test data from about 100 samples with thousands of data points for each and I was able to dynamically select the sample number and range of data I wanted to look at and the charts would update fast. Named ranges with using OFFSET and MATCH. VBA would be fast once you spend the time to learn the syntax and set it up. With that being said, I do use VBA to update the bounds along with minor and major units because I have not found a way to control those on the sheet if the "automatic" are not what you want.

With that, I'm tapping out of this one...not enough time.

 

[jcosta92]

Thanks a lot!!