JordanGoodch
01-04-2012, 05:10 PM
im trying to make it so that a graph can be created using a variable range selected by a user from the selection of dates which are in column A
im using this code below and i get the errors at these lines
ActiveChart.SeriesCollection(1).XValues = Worksheets("Stockpile+Overland").Range("" & Range1)
ActiveChart.SeriesCollection(1).Values = Worksheets("Stockpile+Overland").Range("" & Range1).Offset(0, 4)
Any help is greatly appreciated.
Private Sub CommandButton1_Click()
Worksheets("Stockpile+Overland").Select
Dim rngX As Range
Dim rngX2 As Range
Dim TotRange As Range
Set rngX = Worksheets("Stockpile+Overland").Range("A1:A375").Find(Me.DTPicker1.Value, lookat:=xlPart)
If Not rngX Is Nothing Then
MsgBox "Found at " & rngX.Address
End If
Set rngX2 = Worksheets("Stockpile+Overland").Range("A1:A375").Find(Me.DTPicker2.Value, lookat:=xlPart)
If Not rngX2 Is Nothing Then
MsgBox "Found at " & rngX2.Address
End If
R1 = rngX.Address
R2 = rngX2.Address
Range1 = "" & R1 & ":" & R2
Range("" & R1, "" & R2).Select
Range("" & R1, "" & R2).Offset(0, 4).Select
MsgBox "" & Range1
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Stockpile+Overland").Range( _
"A374:B374"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Worksheets("Stockpile+Overland").Range("" & Range1)
ActiveChart.SeriesCollection(1).Values = Worksheets("Stockpile+Overland").Range("" & Range1).Offset(0, 4)
ActiveChart.SeriesCollection(1).Name = "=""January N/Reclaimer Conveyor"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Jan N-Rec Conveyor"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "January N/Reclaimer Conveyor"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature"
.DisplayBlanksAs = xlNotPlotted
.PlotVisibleOnly = False
.SizeWithWindow = False
End With
End Sub
im using this code below and i get the errors at these lines
ActiveChart.SeriesCollection(1).XValues = Worksheets("Stockpile+Overland").Range("" & Range1)
ActiveChart.SeriesCollection(1).Values = Worksheets("Stockpile+Overland").Range("" & Range1).Offset(0, 4)
Any help is greatly appreciated.
Private Sub CommandButton1_Click()
Worksheets("Stockpile+Overland").Select
Dim rngX As Range
Dim rngX2 As Range
Dim TotRange As Range
Set rngX = Worksheets("Stockpile+Overland").Range("A1:A375").Find(Me.DTPicker1.Value, lookat:=xlPart)
If Not rngX Is Nothing Then
MsgBox "Found at " & rngX.Address
End If
Set rngX2 = Worksheets("Stockpile+Overland").Range("A1:A375").Find(Me.DTPicker2.Value, lookat:=xlPart)
If Not rngX2 Is Nothing Then
MsgBox "Found at " & rngX2.Address
End If
R1 = rngX.Address
R2 = rngX2.Address
Range1 = "" & R1 & ":" & R2
Range("" & R1, "" & R2).Select
Range("" & R1, "" & R2).Offset(0, 4).Select
MsgBox "" & Range1
Charts.Add
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.SetSourceData Source:=Sheets("Stockpile+Overland").Range( _
"A374:B374"), PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = Worksheets("Stockpile+Overland").Range("" & Range1)
ActiveChart.SeriesCollection(1).Values = Worksheets("Stockpile+Overland").Range("" & Range1).Offset(0, 4)
ActiveChart.SeriesCollection(1).Name = "=""January N/Reclaimer Conveyor"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
"Jan N-Rec Conveyor"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "January N/Reclaimer Conveyor"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Temperature"
.DisplayBlanksAs = xlNotPlotted
.PlotVisibleOnly = False
.SizeWithWindow = False
End With
End Sub