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