ADD curves in to existing XY scarter chart using VBA

[nagovind]

Please help by providing a code to generate XY Scartter chart


the criteria is as below


Existing XY Chart is in the sheet ---> PLOT ..here the sheet name is PLOT


XY scarter chart is alreay existing


I need to add more curves into the existing chart


As there are more curves need to be added in to the existing chart, it is hard to type each curve name and the range in to the exiting chart hence the use of VBA code


Existing curves are as below


Name K1


Range X axis ='Calculations(1)'!$P$10:$P$30000

Range Y axis ='Calculations(1)'!$Q$10:$Q$30000


Name K2


Range X axis ='Calculations(1)'!$P$29950:$P$59950

Range Y axis ='Calculations(1)'!$Q$29950:$Q$59950


--------------------------------------------------

Now i need to add new curves as K3, K4 and so on

--------------------------------------------------


For the ranges 59900 ---89900; 89850 -----119850 ; 119800 -----149800 and so on


So to include the above in the already existing XY scarter chart please provide vba CODE


thanks

 

[Hui]

Nagovind


I think the following VBA code will do what you need:


Just remember that you are adding 30,000 points per series and it will add them quickly

and soon your machine will be geologically slow.


In the data file below, You need to add more data in Columns P&Q as the script stops when it finds a blank value every 29950 cells

I deleted the data to save space

[pre]

Sub Add_Series()

Dim SN As Integer
Dim XValue As String, YValue As String, SName As String
Dim i As Long, myRng As Long
Dim WSht As String

WSht = "Plot" 'Your file was  "Calculations(1)"

ActiveSheet.ChartObjects("Plot").Activate

SN = ActiveChart.SeriesCollection.Count + 1

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(SN).Name = "=""K1"""
ActiveChart.SeriesCollection(SN).XValues = "='Calculations(1)'!$P$10:$P$30000"
ActiveChart.SeriesCollection(SN).Values = "'Calculations(1)'!$Q$10:$Q$30000"

i = 2
myRng = 29950

Do While Cells(myRng, 16).Value <> ""

SN = SN + 1

SName = "K" + Trim(Str(i))
XValue = "='" + WSht + "'!$P$" + Trim(Str(myRng)) + ":$P$" + Trim(Str(myRng + 30000))
YValue = "='" + WSht + "'!$Q$" + Trim(Str(myRng)) + ":$Q$" + Trim(Str(myRng + 30000))

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(SN).Name = SName
ActiveChart.SeriesCollection(SN).XValues = XValue
ActiveChart.SeriesCollection(SN).Values = YValue

i = i + 1
myRng = myRng + 29950

Loop

[/pre]

This is mocked up in: https://www.dropbox.com/s/stgqlel25ejk132/AddSeriestoChart.xlsb

 

[nagovind]

Thank you very much HUI

Its perfectly working and i have successfully integrated with my work book

Hats offffff

 

[Hui]

It's a bit different to have to setup the ranges as a String variable especially with multiple " 's