0

Based on "Define range based on the value of another cell" I`m trying to create a flexible macro that updates the range of a graph. This graph needs to update both coordinates but with a fixed column (letter).

Only problem is, that I`m only able to define the end. Not the start, how should I create this flexible (user input) row input field? The first one is what I proposed, but that doesnt work, the others work, but they only define the end not the start. 

Sub Example()
'
'

    Dim Cval As Variant
    Dim Dval As Variant
    Cval = Sheets("Settings").Range("C30").Value
    Dval = Sheets("Settings").Range("C31").Value

    Sheets("Chart-16Q1-18Q4").Select
    ActiveChart.ChartArea.Select
    ActiveChart.PlotArea.Select

    ActiveChart.SeriesCollection(1).Values = "=Calculation_sheet!$C$" & Cval ":$C$" & Dval
    ActiveChart.SeriesCollection(2).Values = "=Calculation_sheet!$D$5:$D$" & Dval
    ActiveChart.SeriesCollection(3).Values = "=Calculation_sheet!$F$5:$F$" & Dval
    ActiveChart.SeriesCollection(4).Values = "=Calculation_sheet!$G$5:$G$" & Dval
    ActiveChart.SeriesCollection(5).Values = "=Calculation_sheet!$H$5:$H$" & Dval
    ActiveChart.SeriesCollection(6).Values = "=Calculation_sheet!$M$5:$M$" & Dval

End Sub
Jan
  • 1
  • 1

1 Answers1

0

Your suggested method should work. You only forgot some & and got a compile error:

ActiveChart.SeriesCollection(1).Values = "=Calculation_sheet!$C$" & Cval & ":$C$" & Dval
'                                                                       ^^^
A.S.H
  • 29,101
  • 5
  • 23
  • 50