Editing charts based on names in VBA

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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:

VBA Code:
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.
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
This:

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

should be this:

Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

This:

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

should be this:

Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Did you try the Union method I posted?
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,809
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Your comma needs to be part of the address string:

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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Your comma needs to be part of the address string:

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

Perfect, thank you!
 

Forum statistics

Threads
1,148,144
Messages
5,745,044
Members
423,917
Latest member
Frank1931

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top