PossiblyNot
New Member
- Joined
- Jul 11, 2014
- Messages
- 5
Hi all,
I'm currently making an excel workbook where a user inputs a range of data which gets processed and reorganised into a table like the one below found in the "Intermediate" sheet in the workbook. There can be anything from 1 series to dozens, even hundreds.
<tbody>
</tbody>
I'm hoping to plot a simple scatter plot to plot vertical ranges (y1 and y2) using VBA to automate the entire process
my current stab at the code as follows
the current error is a 424 error (object required) however, I sense my logic is flawed regardless.
What have I royally fudged?
Cheers
PossiblyNot
I'm currently making an excel workbook where a user inputs a range of data which gets processed and reorganised into a table like the one below found in the "Intermediate" sheet in the workbook. There can be anything from 1 series to dozens, even hundreds.
Name | Y1 | Y2 | X1 | X2 |
Test1 | 21 | 19 | 1 | 1 |
Test2 | 11.6 | 5.4 | 2 | 2 |
Test3 | 2.8 | 2.6 | 3 | 3 |
Test4 | 28.4 | 11.6 | 4 | 4 |
Test5 | 65.4 | 23 | 5 | 5 |
<tbody>
</tbody>
I'm hoping to plot a simple scatter plot to plot vertical ranges (y1 and y2) using VBA to automate the entire process
my current stab at the code as follows
Code:
Sub BioStratPlotter()
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SeriesCollection.NewSeries
Dim i As Variant
Set i.Value = 3
Do While i.Value <> ""
For i = 3 To 48
'each row is a different series, with each row as such: [Name], [Y value], [Y value], [X value], [X value]
ActiveChart.SeriesCollection(6).XValues = "=Intermediate!R(i)$C5:IntermediateR(i)$C6"
ActiveChart.SeriesCollection(6).Values = "=Intermediate!R(i)$C3:IntermediateR(i)$C4"
ActiveChart.SeriesCollection(6).Name = "=Intermediate!R(i)$C2"
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="BioStrat"
'Options for the graph
ActiveChart.Axes(xlValue).MajorGridlines.Select
With ActiveChart.Axes(xlValue)
'Scale is from 0Mya to 65.4 Mya (End-Cretaceous)
.MinimumScale = 0
.MaximumScale = 65.4
.MinorUnitIsAuto = True
.MajorUnit = 10
.Crosses = xlAutomatic
'X-axis is plotting downwards
.ReversePlotOrder = True
.ScaleType = xlAutomatic
.DisplayUnit = xlNone
End With
'loop statement clicks down one row
Next i
Loop
End Sub
the current error is a 424 error (object required) however, I sense my logic is flawed regardless.
What have I royally fudged?
Cheers
PossiblyNot