CODEWITHSUNDEEP
excelvba
user11080542
user11080542

Reputation: 21

Change Chart range using dynamic offset function

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!

Upvotes: 1

Views: 964

Answers (1)

Jon Peltier
Jon Peltier

Reputation: 6063

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

Upvotes: 1

Related Questions