Editing charts based on names in VBA

[RockandGrohl]

Hi guys,

I have a nice one here but I can't quite wrap me head around it.

There are 15 charts on worksheet "grf" and they are handily named "Chart 1", "Chart 2" etc

I'd like to be able to change the source data for each of the sheets which is on sheet "wrk" (Workings)

Here's how I'm attempting to do it:

Dim x As Integer
Dim Rng As Range, Hdr As Range



Set wrk = Worksheets("Workings")
Set grf = Worksheets("Graphs")

LUC = wrk.Range("AR2").Column

Set Hdr = wrk.Range(Cells(2, LUC - 12), Cells(2, LUC - 1))

x = 1

For x = 1 To 15

Set Rng = wrk.Range(Cells(x + 2, LUC - 12), Cells(x + 2, LUC - 1))

ActiveSheet.ChartObjects("Chart " & x & "").Activate
'ActiveChart.SetSourceData Source:=Sheets("Workings").Range("AG2:AQ2,AG7:AQ7") '(Original)
ActiveChart.SetSourceData Source:=Sheets("Workings").Range("Hdr", "Rng")
Next x

The idea here is that Chart 1 corresponds to line 3, Chart 2 corresponds to line 4 etc. The header is on row 2.


The problem I have is that the range(cells()) method clearly only works for a chunk of data and not two separate lines, which is what I need.

If I can R1C1 the range that would help... alas... I can not.

Any ideas? Thanks.

 

[RockandGrohl]

Dim x As Integer
Dim Rng As Range, Hdr As Range
Dim HeadStart As String, HeadEnd As String, DataStart As String, DataEnd As String


Set wrk = Worksheets("Workings")
Set grf = Worksheets("Graphs")

wrk.Activate

LUC = wrk.Range("AR2").Column


'Convert To Column Letter
  HeadStart = Split(Cells(1, LUC - 12).Address, "$")(1)
  HeadEnd = Split(Cells(1, LUC - 1).Address, "$")(1)

grf.Activate
x = 1

For x = 1 To 15

'Set Rng = wrk.Range(Cells(x + 2, LUC - 12), Cells(x + 2, LUC - 1))
ActiveSheet.ChartObjects("Chart " & x & "").Activate
ActiveChart.SetSourceData Source:=Sheets("Workings").Range(HeadStart & 2 & ":" & HeadEnd & 2, HeadStart & x + 2 & ":" & HeadEnd & x + 2)
Next x

Solved, I believe! Had to set the address and then use that in the range.

 

[RoryA]

This:

Set Hdr = wrk.Range(Cells(2, LUC - 12), Cells(2, LUC - 1))

should be this:

Set Hdr = wrk.Range(wrk.Cells(2, LUC - 12), wrk.Cells(2, LUC - 1))

For the other part, you should just be able to use Union(rng, hdr) for the source data.

 

[RockandGrohl]

NOT SOLVED!

When this rattles down, it should be saying the Header (HeadStart - HeadEnd) is AF2:AQ2 - which it does

But it's then not doing two separate ranges, so the next one is "AF3:AQ3", which is fine.

The next one after that is supposed to be Range("AF2:AQ2", AF4:AQ4"), but instead I'm getting "AF2:AQ4") which selects an additional row. I thought I've got the comma separator in there, so how come it's just doing one big range instead of two stacked separate ranges? Thanks.

 

[RockandGrohl]

This:

Set Hdr = wrk.Range(Cells(2, LUC - 12), Cells(2, LUC - 1))

should be this:

Set Hdr = wrk.Range(wrk.Cells(2, LUC - 12), wrk.Cells(2, LUC - 1))

For the other part, you should just be able to use Union(rng, hdr) for the source data.

Hi Rory,

Appreciate this, any chance you could look at the solution I have above which tricked me into thinking it worked, but as you can see by my "NOT SOLVED" message, alas, it has not. Thanks.

 

[RoryA]

Did you try the Union method I posted?

 

[RockandGrohl]

Did you try the Union method I posted?

I fear it may be out of date because I've stepped away from using rng & hdr

The revised code I posted is working perfectly with the exception being that it's updating with a whole range (AF2:AQ6) instead of doing ("AF2:AQ2","AF6:AQ6") - even though my code for this looks like it should work.

ActiveChart.SetSourceData Source:=Sheets("Workings").Range(HeadStart & 2 & ":" & HeadEnd & 2, HeadStart & x + 2 & ":" & HeadEnd & x + 2)

In this instance, x starts as 1 and then iterates up 1 every look, HeadStart is defined as "AF" and HeadEnd is defined as "AQ" - cheers.

 

[RoryA]

Your comma needs to be part of the address string:

ActiveChart.SetSourceData Source:=Sheets("Workings").Range(HeadStart & 2 & ":" & HeadEnd & 2 & "," &  HeadStart & x + 2 & ":" & HeadEnd & x + 2)

 

[RockandGrohl]

Your comma needs to be part of the address string:

ActiveChart.SetSourceData Source:=Sheets("Workings").Range(HeadStart & 2 & ":" & HeadEnd & 2 & "," &  HeadStart & x + 2 & ":" & HeadEnd & x + 2)

Perfect, thank you!