always_confused
Board Regular
- Joined
- Feb 19, 2021
- Messages
- 68
- Office Version
- 2016
- Platform
- Windows
Hello,
I am trying to plot a bunch of time series via a macro I've written. When the column of data are NOT concurrent (like Range("A:A") and Range("C:C")), it works fine. However if I want to plot Range B s Range A (timestamps in A, data in B), then the graph comes out all messed up (all condensed like it's not using the timestamp data at all), and when I select the graph to see what ranges it's using, the timestamp range is not selected. Has anyone encountered this issue or maybe know how to solve this? Here's the sub:
Here's how I' calling it
I am trying to plot a bunch of time series via a macro I've written. When the column of data are NOT concurrent (like Range("A:A") and Range("C:C")), it works fine. However if I want to plot Range B s Range A (timestamps in A, data in B), then the graph comes out all messed up (all condensed like it's not using the timestamp data at all), and when I select the graph to see what ranges it's using, the timestamp range is not selected. Has anyone encountered this issue or maybe know how to solve this? Here's the sub:
VBA Code:
Sub make_plot(name As String, place As Range, data_col As Range, time_col As Range)
'plots data from sheet(name) in range place using data_col and time_col as source
Application.ScreenUpdating = False
Dim chartObj_1 As ChartObject
Dim newChart As Chart
Dim lastrow As Long
lastrow = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Set chartObj_1 = Sheets(name).ChartObjects.Add _
(Left:=place.Left, Width:=place.Width, Top:=place.Top, Height:=place.Height)
Set newChart = chartObj_1.Chart
With newChart
.HasTitle = True
.ChartTitle.Text = data_col.Range("A1").Value
.ChartType = xlXYScatter
.HasLegend = False
.SetSourceData Source:=Application.Union(time_col, data_col)
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).HasMajorGridlines = True
.Axes(xlCategory).AxisTitle.Caption = "Time"
.Axes(xlValue).TickLabels.NumberFormat = "0.000"
.Axes(xlValue).MinimumScale = 8.4
.Axes(xlValue).MaximumScale = 9
With .SeriesCollection(1)
.MarkerStyle = xlMarkerStyleCircle
.MarkerSize = 2
End With
End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub
Here's how I' calling it
VBA Code:
lastrow = Sheets("MySheet").Range("A" & Rows.Count).End(xlUp).Row
Call make_plot("MySheet", Range("K2:S18"), _ 'this one doesn't work correctly
Range("B1:B" & lastrow), Range("A1:A" & lastrow))
Call make_plot("MySheet", Range("K21:S37"), _ 'but this one works fine
Range("C1:C" & lastrow), Range("A1:A" & lastrow))