Help with VBA code to plot a chart using variable ranges

[happy252]

Hi
I've been searching around for a while but couldn't see anything so was hoping to get some help with my problem. I have the following code:

Sub test()

'x-axis is time
Dim x_axis As Range

'y-axis is other variable
Dim y_axis As Range

''To find a column header based on value of A1, when column titles are in row 1 and set to x-axis value
Set x_axis = Columns(WorksheetFunction.Match(Range("A1"), Range("B1:IV1"), 0) + 1)

'To find a column header based on value of A2, when column titles are in row 1 and set to y-axis value
Set y_axis = Columns(WorksheetFunction.Match(Range("A2"), Range("B1:IV1"), 0) + 1)

Set combine = Union(x_axis, y_axis)

combine.Select

ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterLines
ActiveChart.SetSourceData Source:=Range("x_axis,y-axis")

End Sub

This code is linked to a spreadsheet which contains a variable number of columns headings in row 1 and their associated data values from row 2 onwards, I need to plot the values however as the number of column headings vary and are usually in a different order I'm not sure how to reference them when plotting my graph.

When running the current code it matches the values in cells A1 and A2 with the rest of row 1 and highlights 2 columns which match including the column title. What I would like to do is plot these 2 ranges on a graph, however I am getting stuck when setting the source data (I get a 'Method Range of object _ Global failed' error).

Thanks for your help.

 

[happy252]

Bump

 

[Jon Peltier]

The syntax would be

ActiveChart.SetSourceData Source:=Union(x_axis, y_axis)

But if the X data is not to the left of then Y data, it will not give you what you expect.

Also, the X and Y axes are parts of the chart, You should name your variables xData and yData, or xRange and yRange, or something like that.

Do the columns have a header row with a label? Are all columns the same length? Are there any blank cells in a column? These complicate your life.

So try this little bit of code.

Sub test()
  Dim rngUsed As Range, rngData As Range
  Dim rngX As Range, rngY As Range, rngLabel As Range
  Dim iSrs As Long
  Dim cht As Chart
  
  Set rngUsed = ActiveSheet.UsedRange
  ' don't include first row with labels
  Set rngData = rngUsed.Offset(1).Resize(rngUsed.Rows.Count - 1)

  Set rngX = Intersect(Columns(WorksheetFunction.Match(Range("A1"), Range("B1:IV1"), 0) + 1), rngData)

  Set rngY = Intersect(Columns(WorksheetFunction.Match(Range("A2"), Range("B1:IV1"), 0) + 1), rngData)
  Set rngLabel = Columns(WorksheetFunction.Match(Range("A2"), Range("B1:IV1"), 0) + 1).Resize(1)

  Set cht = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart
  
  ' clean up chart
  For iSrs = cht.SeriesCollection.Count To 1 Step -1
    cht.SeriesCollection(iSrs).Delete
  Next
  
  With cht.SeriesCollection.NewSeries
    .Values = rngY
    .XValues = rngX
    .Name = "=" & rngLabel.Address(, , , True)
  End With

End Sub