1

I have already defined the named ranges in my workbook. I want to use the range for placing my pie chart. I am trying to write a code which sets range to variable and move the chart to the specific location.

Dim Rng As Range
Dim ChtObj As ChartObject

Set Rng = ThisWorkbook.Name("BT_GATE1").RefersTo
Set ChtObj = ActiveChart.Parent
ChtObj.Top = Rng.Top

I think I am missing something or using a worng method. Can some one help me assigning a range to variable 'Rng'?

Vidish Soman
  • 15
  • 7
  • `Set rng = ThisWorkbook.Name("BT_GATE1").RefersToRange` – chris neilsen Oct 12 '20 at 05:06
  • Thanks Chris. But I am getting Compile Error as Worng numebr of arguments or invalid property assignment. I want the code to return range 'OUTPUT!$F$44:$I$60' which is assigned to named range 'BT_GATE1'. – Vidish Soman Oct 12 '20 at 05:14
  • Hi Siddharth, I think that will assign values with in 'OUTPUT!$F$44:$I$60' to the range. I think I want some function which will return a string as "OUTPUT!$F$44:$I$60" which I can use further. – Vidish Soman Oct 12 '20 at 05:46
  • I didn't notice this first time, I think it's `Set rng = ThisWorkbook.Names("BT_GATE1").RefersToRange` with an `s` – chris neilsen Oct 12 '20 at 05:51
  • Did you check my answer? Or rather did you try it? – Siddharth Rout Oct 12 '20 at 06:08

1 Answers1

0

A named range is either one cell or a collection of more cells which have been given a name. Ultimately it is a range. So in your case you can directly set it to a range as shown below

Dim Rng As Range

Set Rng = Range("BT_GATE1")

Debug.Print Rng.Address
Debug.Print Rng.Top
Debug.Print Rng.Parent.Name
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Hi Siddharth Yes It is working. I mean at least there is no compile error. My chart is moving but not as I want to. I think I am missing something. Let me know if we can chat in person so that I can share entire code and the output desired. – Vidish Soman Oct 13 '20 at 05:02
  • `Let me know if we can chat in person so that I can share entire code and the output desired. – Vidish Soman 1 hour ago` If you are looking for free consultation then I would recommend posting your questions here so that not only I but others can also chip in and help you out. :) If you are looking for paid consultation then please feel free to click [THIS](http://www.siddharthrout.com/index.php/contact/) – Siddharth Rout Oct 13 '20 at 06:25
  • Thanks you Siddharth. Solution you provided worked. I could also move pie charts as per criteria within specified range with below code `Dim Rng As Range Dim ChtObj As ChartObject Set Rng = ThisWorkbook.Name("BT_GATE1").RefersTo Debug.Print Rng.Address Debug.Print Rng.Top Debug.Print Rng.Parent.Name Set ChtObj = ActiveChart.Parent ChtObj.Top = Rng.Top ChtObj.Left = Rng.Left` But the charts overlap at same point. Is there any way I can place charts randomly with the range? – Vidish Soman Oct 15 '20 at 13:44
  • Please post a new question with the link to this question with more details and screenshots if possible and I or others will definitely reply there :) – Siddharth Rout Oct 15 '20 at 14:49
  • Please post a new question with the link to this question with more details and screenshots if possible and I or others will definitely reply there :) – Siddharth Rout Oct 15 '20 at 14:49