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

[DavidRoger]

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"

 

[hippiehacker]

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

"='Sheet1'!$AQ$30:$AQ$90"

it might be like this

"'Sheet1'!R30C43:R90C43"

 

[DavidRoger]

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.

 

[hippiehacker]

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

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

with

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