Adding Data to Ranges Object

[pauluk13]

Hi,

I want to create multiple graphs in Excel of the same style but with different ranges of data. My plan was to set up a ranges collection that stores a list of ranges. I could then loop through and create a new graph for each one. However I cannot find a way to add values to a ranges object.

Can anyone help?

Paul

 

[RonRepp]

What do you have so far?

Looping through a range can be as easy as this:

Dim i As Long
Range("A1").Activate

For i = 1 To 1000
  
 ActiveCell.Value = i + 1
 ActiveCell.Offset(1, 0).Activate

Next i

This adds consecutive ##s in cell A1 through A1000.

IHTH,


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

My newest novel: Wooden Warriors

http://www.repproductions.net

 

[pauluk13]

So far I have:

    Dim rng As Range
    
    Set rng = Sheets("DL3").Range("B1:F28")
    
    With bk.Worksheets("DL3 Critical").ChartObjects.Add _
            (Left:=0, Width:=500, Top:=0, Height:=300)
        .Chart.SetSourceData Source:=rng
        
    End With

This code makes one graph. I now want to make the same graph but with a completely different range: Range("H1:N28"). I could just re-set the range and run the same code again but I was hoping instead to use rng as a collection of ranges and use a with loop to do the hard work.

Thanks in advance.

 

[combo]

Pick existing chart and change its source:

bk.Worksheets("DL3 Critical").ChartObjects(1).Chart.SetSourceData Source:=Sheets("DL3").Range("N1:N28")

You may need to fully qualify source range ('bk').
It is possible to change source without vba, source can be set as named range, the name can be built or its reference changed in the fly by formulas.

combo

 

[pauluk13]

Oh yeh I have qualified the source bk elsewhere. Thanks.

Wouldn't that change my original graph? What I need is multiple graphs all with the same formatting but displaying different data.

 

[combo]

Sorry, I missed the information that you need to create a series of graphs. So in the loop the position and range are changed. In case of any logic in the ranges it can be inplemented in building address string or refering to range using row and column indexes:

with Sheets("DL3")
    Set rng = Range(.Cells(2,1),.Cells(28,6))
End With

For separated ranges it is possible to find them:

Dim rng1 As Range, rng2 As Range
On Error Resume Next
With Sheets("DL3").UsedRange
    If Not .SpecialCells(xlCellTypeFormulas) Is Nothing Then Set rng1 = .SpecialCells(xlCellTypeFormulas)
    If Not .SpecialCells(xlCellTypeConstants) Is Nothing Then
        If rng1 Is Nothing Then
            Set rng1 = .SpecialCells(xlCellTypeConstants)
        Else
            Set rng1 = Union(rng1, .SpecialCells(xlCellTypeConstants))
        End If
    End If
End With
For Each rng2 In rng1.Areas
    ' create new chart using rng2 as source
Next rng2

combo

 

[irlbinky]

you could try something like

Dim rng As Range
Set rng = Sheets("Sheet1").Range("A1:B4")
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=rng
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"

Set rng = Sheets("Sheet1").Range("A1:B3")
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=rng
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"


this creates 2 pie charts on the same sheet, that are on top of each other but are using 2 different ranges.

 

[SkipVought]

I do a lot of charts. Very often I do charts that result in over 100 different "ranges of data".

I only make ONE CHART.

My chart data is NORMALIZED TABLUAR DATA. I control the data to be plotted by using the AUTO FILTER, either by using the AutoFilter controls or using a DATA > VALIDATION -- LIST and a very few lines of VBA CODE.

ONE CHART.

VERY LITTLE CODE.

HUNDREDS of different chart results!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pauluk13]

Thanks for all your help.

irlblinky I thought it would be possible to instead to do something like:

Dim rng As Ranges

Ranges.Item(1)=("A1:B4")
Ranges.Item(2)=("C14")

for all ranges
Charts.Add
ActiveChart.ChartType = xlPie
ActiveChart.SetSourceData Source:=rng(i)
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
end for

But perhaps I have misunderstood the ranges object.

 

[SkipVought]

It will be much easier for you to perform this IN THE CHART and ON YOUR SHEET.

Use NAMED RANGES.

Organize your data TABULARLY and NORMALIZED. For instance...

ChartGroup x-axis Series1 Series2

Create your chart. Use the Named Ranges in the series assignments in Chart Data dialog.

Filter on ChartGroup to contol chart display "ranges"

Pretty simple.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[irlbinky]

Hi,
you can put my code in a loop with different ranges, I wasnt fully sure of the code for looping the ranges so just wanted to show the range changing and new graphs being created

~cathal

 

[pauluk13]

In the end I just irlblinky's code in a function and repeated for all the ranges. Thanks all!