I am trying to chart data from a worksheet, but only want certain rows (i.e. rows that only have numbers > 10 in col C). My chart needs 2 axis'.
About the best way I figured on how to do it is to set up 3 Range variables for each piece of data (1 for series 1 data, 1 for series 2 data, 1 for series 1 x-axis) and fill them using the union command. The variable seem to be filling fine.
When I try to then use the range variables, an odd thing occurs. If only one value is stored under the range, it works fine; however, if more than one value exists in the range, I get an unable to set the XValues property of the series class'.
Strange thing, with multiple values in the range, it works for placing the data in the chart, but does not work for the .XValue
Here is how I am filling the Range variable...
Dim myCell As Range
Dim myARange As Range, myDRange As Range, myYRAnge As Range
Set myDRange = Nothing
Set myTRange = Nothing
Set myYRAnge = Nothing
For iRow = 2 To FinalRow
Set myCell = ActiveSheet.Cells(iRow, "B")
If myCell.Font.ColorIndex = 3 Then
' cell is okay, so add to charting Arrays
If myDRange Is Nothing Then 'Can't union an empty variable - need to copy cell if empty
Set myDRange = myCell
Set myTRange = myCell.Offset(0, 4)
Set myYRAnge = myCell.Offset(0, 5)
Else
Set myDRange = Union(myDRange, myCell)
Set myTRange = Union(myTRange, myCell.Offset(0, 4))
Set myYRAnge = Union(myYRAnge, myCell.Offset(0, 5))
End If
End If
Next
-----------------------------------------------------------
Here is how I am attempting to chart it.
Range("A" & FinalRow + 2).Select
Sheetname = ActiveSheet.Name
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"
'Charts.Select
ActiveChart.SetSourceData Source:=myYRAnge, PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = myDRange 'THIS DOES NOT WORK WITH MULTIPLE VALUE IN RANGE
ActiveChart.SeriesCollection(1).Name = "=""Yield %"""
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered ' Converts series one to bar
ActiveChart.SeriesCollection(2).Values = myTRange ! THIS ALSO DOES NOT WORK WITH MULTIPLE VALUES IN RANGE
ActiveChart.SeriesCollection(2).Name = "=""Total Ran"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheetname ' Convert from tab to embedded chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheetname & " - > 10 ONLY" ' Top of Chart Name
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
' .Axes(xlCategory, xlSecondary).HasTitle = False
' .Axes(xlValue, xlSecondary).HasTitle = False
End With
Thanks for your time!
-hj
About the best way I figured on how to do it is to set up 3 Range variables for each piece of data (1 for series 1 data, 1 for series 2 data, 1 for series 1 x-axis) and fill them using the union command. The variable seem to be filling fine.
When I try to then use the range variables, an odd thing occurs. If only one value is stored under the range, it works fine; however, if more than one value exists in the range, I get an unable to set the XValues property of the series class'.
Strange thing, with multiple values in the range, it works for placing the data in the chart, but does not work for the .XValue
Here is how I am filling the Range variable...
Dim myCell As Range
Dim myARange As Range, myDRange As Range, myYRAnge As Range
Set myDRange = Nothing
Set myTRange = Nothing
Set myYRAnge = Nothing
For iRow = 2 To FinalRow
Set myCell = ActiveSheet.Cells(iRow, "B")
If myCell.Font.ColorIndex = 3 Then
' cell is okay, so add to charting Arrays
If myDRange Is Nothing Then 'Can't union an empty variable - need to copy cell if empty
Set myDRange = myCell
Set myTRange = myCell.Offset(0, 4)
Set myYRAnge = myCell.Offset(0, 5)
Else
Set myDRange = Union(myDRange, myCell)
Set myTRange = Union(myTRange, myCell.Offset(0, 4))
Set myYRAnge = Union(myYRAnge, myCell.Offset(0, 5))
End If
End If
Next
-----------------------------------------------------------
Here is how I am attempting to chart it.
Range("A" & FinalRow + 2).Select
Sheetname = ActiveSheet.Name
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"
'Charts.Select
ActiveChart.SetSourceData Source:=myYRAnge, PlotBy:=xlColumns
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = myDRange 'THIS DOES NOT WORK WITH MULTIPLE VALUE IN RANGE
ActiveChart.SeriesCollection(1).Name = "=""Yield %"""
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered ' Converts series one to bar
ActiveChart.SeriesCollection(2).Values = myTRange ! THIS ALSO DOES NOT WORK WITH MULTIPLE VALUES IN RANGE
ActiveChart.SeriesCollection(2).Name = "=""Total Ran"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheetname ' Convert from tab to embedded chart
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheetname & " - > 10 ONLY" ' Top of Chart Name
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
' .Axes(xlCategory, xlSecondary).HasTitle = False
' .Axes(xlValue, xlSecondary).HasTitle = False
End With
Thanks for your time!
-hj