Change Chart range using dynamic offset function

Question

I am attempting to use VBA in Excel to offset a series of charts by moving their data range down one row (I.E Week 1 will be A:74 and C:74, week 2 will be A:75 and C:75 etc)

I'm new to VBA and figured a simple Offset command would meet my needs but none of the code I put together will run without errors.

Here are some examples of Code I attempted:

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.SetSourceData Source:=Offset(1, 0)

ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.Offset(1, 0).Select

I expected the Range of the Active Chart or Cell would have been Offset by 1 Row, 0 columns.

However, the first attempt gave the error:

Compile Error:
Sub or Function not defined

The second attempt gave the error:

Compile Error:
Method or data member not found

Not sure where to go with the errors as most of the other pages I looked at involve setting specific cell ranges for the offset function and I need this to ignore hard coded ranges and dynamically look at the ranges the current chart has selected and just move it all down by one, basically going from Weeks 2 - 8 to Weeks 3 - 9.

Any help would be greatly appreciated!

Offset is a property of the Range object. So you need to define your source data using a range variable and then offset that. — SJR
– SJR, Commented Feb 18, 2019 at 18:19
Will the range you are using maybe be the last used row in your range? If so you can have your chart continuously update by that condition. Although if that is the case, I would like to point out that you do not need VBA to do this. You can use OFFSET excel sheet equation to get same similar results — urdearboy
– urdearboy, Commented Feb 18, 2019 at 18:52
So I tried to flesh is out a bit more, below is what I put together to define the data source and then use the Range.Offset function: Worksheets("Trend Charts").Activate ActiveSheet.ChartObjects("Chart 1").Activate Range.Offset(1, 0).Activate It produced the error "Argument not optional". These will be the end row for each table that I'm looking to update, but the Offset function doesn't look like it can handle the field values that I am setting. — user11080542
– user11080542, Commented Feb 18, 2019 at 20:41
Oddly enough it appears the debugged is referencing the very top line of the Macro in yellow: Sub Macro2() Not sure what that means, however I think the below code should be working: ActiveChart.SetSourceData Source:=Range.Offset(1, 0).Select — user11080542
– user11080542, Commented Feb 19, 2019 at 17:05

Jon Peltier · Accepted Answer · 2019-02-28 02:43:49Z

Assuming the X data is in A74 and the Y data in C74, your series formula would look something like this:

=SERIES(Sheet1!$C$73,Sheet1!$A$74,Sheet1!$C$74,1)

You need to parse this formula, find the X and Y ranges (the second and third arguments), offset them, and put them back into the series. This procedure did it for me.

Sub MoveChartDataDownOneRow()
  With ActiveChart
    Dim srs As Series
    For Each srs In .SeriesCollection
      Dim sFmla As String
      sFmla = srs.Formula
      ' =SERIES(Sheet1!$C$73,Sheet1!$A$74,Sheet1!$C$74,1)
      Dim sArgs As String
      sArgs = Mid$(sFmla, Len("=series(") + 1)
      sArgs = Left$(sArgs, Len(sArgs) - 1)
      ' Sheet1!$C$73,Sheet1!$A$74,Sheet1!$C$74,1
      Dim vArgs As Variant
      vArgs = Split(sArgs, ",")
      ' Array("Sheet1!$C$73","Sheet1!$A$74","Sheet1!$C$74","1")
      Dim XRange As Range, YRange As Range
      Set XRange = Range(vArgs(LBound(vArgs) + 1))
      Set YRange = Range(vArgs(LBound(vArgs) + 2))
      ' offset ranges
      Set XRange = XRange.Offset(1)
      Set YRange = YRange.Offset(1)
      ' reassign to series
      srs.XValues = XRange
      srs.Values = YRange
    Next
  End With
End Sub

Collectives™ on Stack Overflow

Change Chart range using dynamic offset function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related