Dynamic Charts
 

I have a 2 column data set that I need to chart. The last row of this
dataset can increase or decrease. I need the chart to reflect the values
dynamically.

For this, I am calculating the last row value in the macro and transferring
it to the variable "lr".

When I try to introduce the value of lr in the following statement and run
it, I get an error "Run-time error '1004': Method 'run' of object
'_application' failed"

activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr & "),
plotby:=xlcolumns


Does anyone know how to resolve this problem?

Your syntax is wrong. Remove the last quote:

activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr),
plotby:=xlcolumns

But this kind of thing can be done without using VBA. You can create a
named range with a formula that detects the number of rows, and adjusts
accordingly:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


vrk1 wrote:

I have a 2 column data set that I need to chart. The last row of this
dataset can increase or decrease. I need the chart to reflect the values
dynamically.

For this, I am calculating the last row value in the macro and transferring
it to the variable "lr".

When I try to introduce the value of lr in the following statement and run
it, I get an error "Run-time error '1004': Method 'run' of object
'_application' failed"

activechart.setsourcedata source:=sheets(custname).range("c1:d" & lr & "),
plotby:=xlcolumns


Does anyone know how to resolve this problem?

Thank you Jon. I need to do this via VBA as this is a client requirement.

I removed the last quote like you suggested and I am getting the following
error:

"Run Time Error 91: Object Variable or withblock variable not set."


I played around with this a bit and also tried replacing the above statement
with the following. But this time, I got a different error "Run Time Error
1004: Method Run of Object '_Application' failed. Can you help please?
Thanks for your time!


Set myrange = Range("c1:c3, d1:d" & lr)
With ActiveChart
.SetSourceData Source:=Sheets(custname).Range(myrange),
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:=custname
.HasTitle = True
.ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

1004: Method Run of Object '_Application'

Are you using Application.Run anywhere?

Also, this is going to give you problems: Range("c1:c3, d1:d" & lr)

Do you mean Range("c1:d" & lr) ?

This is how your little snippet of code should look. Define the
worksheet-qualified range first, then just use the range variable in
SetSourceData

Sub Test123()
Dim myrange As Range
Dim lr As Long
Dim custname As String

lr = 4
custname = "Sheet1"
Set myrange = Worksheets(custname).Range("c1:d" & lr)
With ActiveChart
.SetSourceData Source:=myrange, PlotBy:=xlColumns
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

vrk1 wrote:
Thank you Jon. I need to do this via VBA as this is a client requirement.

I removed the last quote like you suggested and I am getting the following
error:

"Run Time Error 91: Object Variable or withblock variable not set."


I played around with this a bit and also tried replacing the above statement
with the following. But this time, I got a different error "Run Time Error
1004: Method Run of Object '_Application' failed. Can you help please?
Thanks for your time!


Set myrange = Range("c1:c3, d1:d" & lr)
With ActiveChart
.SetSourceData Source:=Sheets(custname).Range(myrange),
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:=custname
.HasTitle = True
.ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

That worked Jon! Thanks for your time.


"Jon Peltier" wrote:

1004: Method Run of Object '_Application'

Are you using Application.Run anywhere?

Also, this is going to give you problems: Range("c1:c3, d1:d" & lr)

Do you mean Range("c1:d" & lr) ?

This is how your little snippet of code should look. Define the
worksheet-qualified range first, then just use the range variable in
SetSourceData

Sub Test123()
Dim myrange As Range
Dim lr As Long
Dim custname As String

lr = 4
custname = "Sheet1"
Set myrange = Worksheets(custname).Range("c1:d" & lr)
With ActiveChart
.SetSourceData Source:=myrange, PlotBy:=xlColumns
End With
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

vrk1 wrote:
Thank you Jon. I need to do this via VBA as this is a client requirement.

I removed the last quote like you suggested and I am getting the following
error:

"Run Time Error 91: Object Variable or withblock variable not set."


I played around with this a bit and also tried replacing the above statement
with the following. But this time, I got a different error "Run Time Error
1004: Method Run of Object '_Application' failed. Can you help please?
Thanks for your time!


Set myrange = Range("c1:c3, d1:d" & lr)
With ActiveChart
.SetSourceData Source:=Sheets(custname).Range(myrange),
PlotBy:=xlColumns
.Location Whe=xlLocationAsObject, Name:=custname
.HasTitle = True
.ChartTitle.Characters.Text = UserForm1.title1 & " - " & custname
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With