I want to plot a bunch a of data in VBA. my X axis is in "YYYY/MM/DD h:mm" format. when I plot it using XLline, it plots all the data from different times of one day under one X tang based on one day. for example it shows two vertical lines for dozens of data for two different days.
when I change the graph type to XLxyscatter, the problem is solved but I have no control on editing the chart as I need to control the minimum-maximum of Y axis and major units on X axis.
here is my code:
Dim h2s As ChartObject
Dim h2ss As Chart
Dim h2sso As SeriesCollection
Dim h2sso2 As Series
Set h2s = Sheet3.ChartObjects.Add(Range("a1").Left, Range("A56").Top, 479, 300)
h2s.Name = "Air/ Acid gas flow"
Set h2ss = h2s.Chart
With h2ss
.HasLegend = False
.HasTitle = True
.ChartTitle.Text = "H2S + SO2 in Tailgas"
*******************
Dim rng3 As Range
Dim sds4 As Double
Set rng3 = Sheets(2).Range(Sheets(2).Cells(2, 16), Sheets(2).Cells((finishdate - startdate), 16))
sds4 = Application.WorksheetFunction.Min(rng3)
sdss2 = Application.WorksheetFunction.Max(rng3)
.Axes(xlValue).MinimumScale = (sds4 - (sds4 * 0.1))
.Axes(xlValue).MaximumScale = (sdss2 + sdss2 * 0.1)
.Axes(xlCategory).TickLabels.NumberFormat = "m/d/yyyy h:mm"
************************
Set h2sso = .SeriesCollection
Set h2sso2 = h2sso.NewSeries
With h2sso2
.Name = "H2S+SO2 in Tailgas"
.XValues = Sheets(1).Range(Sheets(1).Cells(startdate, 1), Sheets(1).Cells(finishdate, 1))
.Values = Sheets(2).Range(Sheets(2).Cells(2, 16), Sheets(2).Cells((finishdate - startdate), 16))
.ChartType = xlXYScatterLines
End With
End With
Basically when I choose XLxyscatter the part between starts is not run. Can someone help me please?
Attached you can see the Xlline and XYscatter results from the code.
scatter.jpg
line.jpg
Bookmarks