Results 1 to 4 of 4

Using VBA to change the graph series values...Need help

This is a discussion on Using VBA to change the graph series values...Need help within the Excel Questions forums, part of the Question Forums category; Hi, I get macro A to work but with same concept the macro B doesn't seem to work. What ever ...

  1. #1
    New Member
    Join Date
    Oct 2011
    Posts
    33

    Default Using VBA to change the graph series values...Need help

    Hi,
    I get macro A to work but with same concept the macro B doesn't seem to work. What ever values added after = will be show in the graph series values. I have try function text and value but only show same result. Series value shows ='Sheet1'!$AD$22 instead of ='Sheet1'!$AQ$30:$AQ$90.

    Macro A:
    ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [R20]

    Macro B:
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD20]

    Below is the macro I get from recording:
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(3).Name = "=""Line 1"""
    ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$30:$AQ$90"

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,726

    Default Re: Using VBA to change the graph series values...Need help

    try to use RC formulas r= row and C = column

    Code:
    "='Sheet1'!$AQ$30:$AQ$90"
    it might be like this

    Code:
    "'Sheet1'!R30C43:R90C43"

  3. #3
    New Member
    Join Date
    Oct 2011
    Posts
    33

    Default Re: Using VBA to change the graph series values...Need help

    Hi,

    This is a renew question.

    I am working on graph auto update for X-axis. Macro A is for graph format and Macro B is for X-axis.

    Macro A:
    ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [R2]

    Macro B:
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD2]

    AD2 = 'Sheet1'!$AQ$3:$AQ$9

    Macro A is working but cant get the macro B to work. What ever values added after macro B's = will be show in the graph series values. So there are no link to the spreadsheet. Series value shows ='Sheet1'!$AD$2 instead of ='Sheet1'!$AQ$3:$AQ$9

    Below is the macro I get from recording:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(3).Name = "=""Line 1"""
    ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$3:$AQ$9"

    May I know what syntax should be replacing [AD2] since it refer to a formula instead of a value? I get this from other forum.

    I need [AD2] to be link to the excel spreadsheet because any changes can be directly change in the spreadsheet.

    Thank you.

  4. #4
    Board Regular
    Join Date
    Aug 2011
    Location
    AMS
    Posts
    1,726

    Default Re: Using VBA to change the graph series values...Need help

    Quote Originally Posted by DavidRoger View Post
    Hi,

    This is a renew question.

    I am working on graph auto update for X-axis. Macro A is for graph format and Macro B is for X-axis.

    Macro A:
    ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = [R2]

    Macro B:
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD2]

    AD2 = 'Sheet1'!$AQ$3:$AQ$9

    Macro A is working but cant get the macro B to work. What ever values added after macro B's = will be show in the graph series values. So there are no link to the spreadsheet. Series value shows ='Sheet1'!$AD$2 instead of ='Sheet1'!$AQ$3:$AQ$9

    Below is the macro I get from recording:

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(3).Name = "=""Line 1"""
    ActiveChart.SeriesCollection(3).Values = "='Sheet1'!$AQ$3:$AQ$9"

    May I know what syntax should be replacing [AD2] since it refer to a formula instead of a value? I get this from other forum.

    I need [AD2] to be link to the excel spreadsheet because any changes can be directly change in the spreadsheet.

    Thank you.
    try to replace
    Code:
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = [AD2]
    with

    Code:
    ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(3).Values = "=Sheet1!R3C43:R9C43"

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com