Problem using range variable for chart .XValues

[HerbJones]

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

 

[AlphaFrog]

Union your three ranges and set that as the source data.

    [color=red]Set myAllRange = Union(myDRange, myTRange, myDRange)[/color]
    [color=green]'-----------------------------------------------------------[/color]
    [color=green]'Here is how I am attempting to chart it.[/color]

    [color=green]'Range("A" & FinalRow + 2).Select[/color]
    Sheetname = ActiveSheet.Name
    Charts.Add
    ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Lines on 2 Axes"
    [color=green]'Charts.Select[/color]
    ActiveChart.SetSourceData Source:=[COLOR="Red"]myAllRange[/COLOR], PlotBy:=xlColumns
[color=green]'    ActiveChart.SeriesCollection.NewSeries[/color]
[color=green]'    ActiveChart.SeriesCollection(1).XValues = myDRange    'THIS DOES NOT WORK WITH MULTIPLE VALUE IN RANGE[/color]
    ActiveChart.SeriesCollection(1).Name = "=""Yield %"""
    ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered    [color=green]' Converts series one to bar[/color]
[color=green]'    ActiveChart.SeriesCollection(2).Values = myTRange    '! THIS ALSO DOES NOT WORK WITH MULTIPLE VALUES IN RANGE[/color]
    ActiveChart.SeriesCollection(2).Name = "=""Total Ran"""
    ActiveChart.Location Where:=xlLocationAsObject, Name:=Sheetname    [color=green]' Convert from tab to embedded chart[/color]
    [color=darkblue]With[/color] ActiveChart
        .HasTitle = [color=darkblue]True[/color]
        .ChartTitle.Characters.Text = Sheetname & " - > 10 ONLY"    [color=green]' Top of Chart Name[/color]
        .Axes(xlCategory, xlPrimary).HasTitle = [color=darkblue]False[/color]
        .Axes(xlValue, xlPrimary).HasTitle = [color=darkblue]False[/color]
        [color=green]' .Axes(xlCategory, xlSecondary).HasTitle = False[/color]
        [color=green]' .Axes(xlValue, xlSecondary).HasTitle = False[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[HerbJones]

Thanks for the help - it is sincerely appreciated!

I can't quite get your changes to work. I may not have explained it correctly - it's hard in a forum, please bear with me.

The 1st SourceValue was one of the things that was working with my original example.

I was trying to make a 2-axis graph (one bar the other line).

It was the 2nd axis that I can't get to work with the union command.

The fix you mention now causes a "Method ;SeriesCollection' of object '_Chart' failed - on line "ActiveChart.SeriesCollection(2).Select"

The 1st axis series data was previously importing fine, but the code was puking on setting XValues for both series' and for importing the SourceValue the 2nd series.


On a side note - I find it amazing the code that I am able to write after just one week of reading VBA and Macros for Excel 2007 - thanks guys! You saved me an overpriced class at the local tech, and a ton of time via a condensed method of learning!

 

[HerbJones]

Another approach...

Is there a way to 'watch' a range?

When I am running my code, and watching myDRange, for example, the first entry goes in fine, but when I 'union' the next value to it (with my original code), the value field blanks. Shouldn't this show the two values?

I agree, given that one value works, more than one does not - my problem has to be in the way that I am using union.

I can't find a way to 'watch' a range's contents while using union - it blanks the value field as mentioned above. Is this normal? Is there another way to view contents of a range?

Thanks again!

 

[HerbJones]

I think I know the problem - but can't figure out a solution.

I think the issue is that I am pushing the contents of mycell into the range, instead of the cell's location.

It looks like having data in the range works for Source:=, but not for .XValues

I can see two ways to fix it - modify the code the push cell location into the range (instead of contents)... or find a different way all together.

Is there an easier way that I may not be realizing?

I basically want to filter rows, and append a chart of the filtered data, without changing the appearance of the raw data.