SetSourceData syntax for Range in Chart

[abitslow]

Hi,
having defined a term "LastRow" for the value of the last row of data in a column, I tried the following which doesn't seem to work.

LastRow = Sheets("Grid_ML").Range("O2").Value
Sheets("Print_Sheet").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection.Add _
    Source:=Worksheets("Grid_ML").[b]Range(Cells(2, 7), Cells(LastRow, 8))[/b], _
    PlotBy:=xlColumns

Can I refere to a range in this way or have I gone wrong and I must use some other means of defining it? The range itself may vary in length depending on user input.
Many thanks.

 

[JensKKK]

abitslow,

I did not come across the commands that you are using in your code example, but you can define a range as:

Source:=Worksheets("Grid_ML").Cells(2, 7). resize(LastRow, 8)),

Hope this helps.

 

[Bong]

Assuming LastRow is set to an integer, then, yes, your range specification is valid.

_________________
Bob Rashkin

 

[abitslow]

Thanks both. Still can't get it to work though!

Bob, to define it as an integer I just added:

Dim LastRow As Integer

Jens, your code doen't cause it to go to the debug and highlight the code in yellow (like mine does) but the chart remains blank.

I am using a work around by defining the Range as

Source:= Sheets("Grid_ML").Range("G2:H32000")
ActiveChart.PlotVisibleOnly = True

but I feel like that is a failure!
Thanks again.
m

 

[Rofeu]

Hi,

if Source:= Sheets("Grid_ML").Range("G2:H32000") works, try one of the following:

With Worksheets("Grid_ML")
    Source:=.Range(.Cells(2, 7), .Cells(LastRow, 8))
End With

or

Source:=Worksheets("Grid_ML").Range(Cells(2, 7).Address & ":" & Cells(LastRow, 8).Address)

Cheers,

Roel

 

[PHV]

What is the value displayed in O2 in the Grid_ML sheet ?
FYI a row number is a Long (not an Integer).

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abitslow]

PHV, the contents of O2 is a number (1742 currently) that is the result of a count of the number of rows in the columns I'm interested in plotting (G:H) and is put there when a previous macro runs. (Hope that makes sense).

This doesn't work:

Dim LastRow As Long
LastRow = Sheets("Grid_ML").Range("O2").Value + 1
Sheets("Print_Sheet").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.ChartArea.ClearContents
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    With ActiveChart
        .SetSourceData Source:=Sheets("Grid_ML").Range(Cells(2, 7), Cells(LastRow, 8))
        .PlotVisibleOnly = True

This does work

Dim LastRow As Long
LastRow = Sheets("Grid_ML").Range("O2").Value + 1
Sheets("Print_Sheet").Activate
    ActiveSheet.ChartObjects("Chart 2").Activate
    ActiveChart.ChartArea.ClearContents
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    With ActiveChart
        .SetSourceData Source:=Sheets("Grid_ML").Range("G2:H32000")
        .PlotVisibleOnly = True

Roel - thanks, but I got no joy trying your solution. It may well be me!

 

[PHV]

And this ?
SetSourceData Source:=Sheets("Grid_ML").Range(Sheets("Grid_ML").Cells(2, 7), Sheets("Grid_ML").Cells(LastRow, 8))

Or this ?
SetSourceData Source:=Sheets("Grid_ML").Range("G2:H" & LastRow)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rofeu]

Hi,

I've rewritten the code from your first post:

Dim LastRow As Integer
Dim DataRange As Range

With Sheets("Grid_ML")
LastRow = .Range("O2").Value
Set DataRange = .Range(.Cells(2, 7), .Cells(LastRow, 8))
End With

Sheets("Print_Sheet").ChartObjects("Chart 1").Chart.SeriesCollection.Add Source:=DataRange, RowCol:=xlColumns

Set DataRange = Nothing

This works for me. That PlotBy you have, I couldn't find anywhere.

Cheers,

Roel

 

[SkipVought]

The parameter is Rowcol not PlotBy.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[abitslow]

PHV - Success!
Both your suggestions work. Many thanks indeed.

Roel - I found PlotBy when I recorded a macro of making the chart. I run Excel 2003.
Interestingly, I pasted in your code and it produced a diagonal line across the chart! I'll try and get it to work when I have time.

Many thanks again.
m

 

[SkipVought]

abitslow,

Are you ADDING a new chart each time your source data range changes? Not a good idea IMHO.

I rarely ADD charts using VBA. I set them up the way I want using the chart wizard.

However, I often use techniques to make the chart react to dynamic range changes in the source data.

Two different approches.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[abitslow]

That is good sense Skip. I'm afraid my VBA/Excel knowledge is incredibly basic - I'm pretty much at my limit with what I achieved already! I'll go away and have a think on how to do that though, so thanks for the tip.

 

[SkipVought]

Check out the Series tab in the Data Source.

All you need do is assign each series' Value parameter and the XValue parameter.

I use Dynamic Named Ranges faq68-1331.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]