Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Scaling for Charts | Charts and Charting in Excel | |||
dynamic charts | Charts and Charting in Excel | |||
Can't create dynamic charts | Charts and Charting in Excel | |||
Dynamic Charts | Excel Discussion (Misc queries) | |||
Can I post dynamic line charts by swapping out .cvs files??? | Charts and Charting in Excel |