Cant refer to graph from other sheet?

[JKB]

Hi everybody!

I want to refer to a graph from my sheet called "Overview", the graph is located in a sheet called "Historic". The code is the following:

Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lInterval As Long: lInterval = shtHis.Range("A2").End(xlDown).Row


shtHis.ChartObjects("Chart 11").Activate
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

The code is located in a module for the whole workbook.
When i push the button to run the code in "overview" i get a bug: "Run-time error "1004": Method "range" of Object" worksheet" failed.

Im not very familiar with graphs within VBA, am i refering to it the wrong way?
- When i press f8 within the vba window, in compile mode, the code works just fine.

Hope someone can help! Cheers!

[Aflatoon]

Which line is highlighted by the debugger? The error message is not related to charts.

BTW, it's generally safer to use End(xlUp):

lInterval = shtHis.Cells(shtHis.Rows.Count, "A").End(xlUp).Row

and you don't need to activate the chart:

shtHis.ChartObjects("Chart 11").Chart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

[JKB]

Ah thank you for the tip!

The line which is bugging, is the following:
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

The code above the bugging line is the following, it doesn't seem to have any problems running these:
Sub Adjust_graphs()


Dim shtHis As Worksheet: Set shtHis = Sheets("Historic")
Dim lInterval As Long: lInterval = shtHis.Range("A2").End(xlDown).Row


shtHis.ChartObjects("Chart 11").Activate
ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval)

[Aflatoon]

What's the actual value of lInterval when the error is shown?

[Paul_Hossler]

Try something like this, That wasn't the correct way to specify a Range (as you found out)

Note the colon in the .Range

Option Explicit

Sub Macro1()
    Dim L As Long
    
    L = 8
    
    Worksheets("Sheet2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Worksheets("Sheet1").Range("A1:B" & L)
End Sub

[JKB]

I checked under "Locals" it says 1238, which is my last observation, so it should be ok!?

[JKB]

Hi Paul!
The reason for why i have written it as i have, is because i dont have 1 graph but 4!
Im using Column A,B for graph 1, Column A, C for graph 2, A,D for graph 3 and A,E as input for graph 4

The weird thing is that the code goes into debug mode, when launching from the button, but when i launch it in the compile mode, it works fine!

[Aflatoon]

The code is fine - just tested running from a button without issue. What kind of button is it and where is it located?

[Paul_Hossler]

Ok, thanks for the additional information

Then you'd want to do something like this

Option Explicit

 
Sub Macro1()
    Dim L As Long
    Dim ws As Worksheet
     
    Set ws = Worksheets("Sheet1")
    
    L = 8
     
     'ActiveChart.SetSourceData Source:=shtHis.Range("A1:A" & lInterval & ",B1:B" & lInterval) -- wrong way
     
     
    Worksheets("Sheet2").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("B1:B" & L))

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("C1:C" & L))
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("D1:D" & L))
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatter
    ActiveChart.SetSourceData Source:=Union(ws.Range("A1:A" & L), ws.Range("E1:E" & L))
 
End Sub

[JKB]

Thanks man! And good news that im not completely useless at this! But the button you used to launch the code, was it in another sheet than where the code took in the data? I think that might be my problem.

The button is located in another sheet called Overview, all the data related to the button is in a specific sheet called "Historic"
The button i just created using insert and form controls, the code for it is located under "Module1"

[Aflatoon]

I tested it with the button on another sheet and it still works without problem in 2010.

To be clear: there is nothing wrong with the way you are referring to the range. It is perfectly valid syntax
since it resolves to this:

shtHis.Range("A1:A1238,B1:B1238")

[JKB]

Yeah i think i get you! Now i just have to figure out, whether i placed some code in the wrong module or something like that - i suppose

[Paul_Hossler]

Never mind, forgot the comma

[Aflatoon]

You missed the comma before the B1.

[Paul_Hossler]

U B 2 fast

I was typing as fast as I could to cover my Oops

[Aflatoon]