(Solved) – Create same chart with fewer selections


I have created a chart using the following code. It consists of two columns, one with positive and negative price values, the other with the numbers 1 through to 60.

The way I have made this chart is essentially with 2 different series. Within each series, I have selected 2 ranges to form the curve. These 2 curves will then meet at y=0, to give the impression that it is just one big curve, when essentially it is 2 chart series meeting together.

The main aim of this is to determine the positive values from the negative values in the graph; by using effectively 2 different curves, I am able to use different colours for each curve to distinguish the positive and negative values.

What I am looking to do now, is recreate the same graph, however instead of using 4 selections like below, I would like to be able to use 2 selections. I am not sure how you would go about doing this,so any advice or suggestions would be great. I have been told to look over selecting parts of an array and looping them, but I am having a fair amount of difficulty.

Thanks very much in advance!

Dim xData As Range
    Dim yData As Range
    Dim x2Data As Range
    Dim y2Data As Range

    Dim serName As Range

    'set the ranges to get the data and y value label
    Set xData = Application.Selection
    Set yData = Application.Selection
    Set x2Data = Application.Selection
    Set y2Data = Application.Selection
    Set serName = Range("AY2")

    'define range
    Set xData = Application.InputBox("xvalues", , xData.Address, Type:=8)
    Set yData = Application.InputBox("yvalues", , yData.Address, Type:=8)
    Set x2Data = Application.InputBox("x2values", , x2Data.Address, Type:=8)
    Set y2Data = Application.InputBox("y2values", , y2Data.Address, Type:=8)

    'get reference to ActiveSheet
    Dim sht As Worksheet
    Set sht = ActiveSheet

    'create a new ChartObject at position (4100, 195) with width 400 and height 300
    Dim chtObj As ChartObject
    Set chtObj = sht.ChartObjects.Add(4100, 195, 400, 300)

    'get reference to chart object
    Dim cht As Chart
    Set cht = chtObj.Chart

    'create the new series
    Dim ser As Series
    Set ser = cht.SeriesCollection.NewSeries

    ser.Values = yData
    ser.XValues = xData
    ser.Name = positivedcf
    ser.ChartType = xlXYScatterLines

    'create second series

    Dim ser2 As Series
    Set ser2 = cht.SeriesCollection.NewSeries

    ser2.Values = y2Data
    ser2.XValues = x2Data
    ser2.Name = negativedcf
    ser2.ChartType = xlXYScatterLines

Leave a Reply

Your email address will not be published. Required fields are marked *