range variable won't assign (chartobject.topleftcell property)

  • Thread starter Thread starter Matthew Dodds
  • Start date Start date
M

Matthew Dodds

Folks,

This one has me stumped; can an expert ride to the rescue please!!

In the code below the assignment marked **** doesn't work; rngTopLeft
remains resolutely NOTHING in the locals window. In an attempt to
trouble shoot I've added various debug statements to see whether the
information demanded by the assignment is available, and all print the
expected info to immediate window.

Suggestions, please, as to why the assignment to rngTopLeft doesn't
happen

Many thanks in anticipation

Matt


On Error Resume Next 'currClust not yet assigned, n'oubliez pas
If Not rngCluster = rngCurrentCluster Then 'change H & V offset
for charts in new cluster
If iChart = 1 Then 'chart was the very first chart
iHoffset = iHoffset + iXsize
iVoffset = 0
iChart = iChart + 1
**** rngTopLeft =
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).TopLeftCell

Debug.Print ActiveSheet.Name: '->SHEET CORRECT
Debug.Print ActiveSheet.ChartObjects.Count: '->NUMBER CORRECT (1)
Debug.Print ActiveSheet.ChartObjects
_(ActiveSheet.ChartObjects.Count).TopLeftCell.Address: '->CORRECT
ADDRESS
Debug.Print TypeName(ActiveSheet.ChartObjects
_(ActiveSheet.ChartObjects.Count).TopLeftCell): '-> it's a range, now
there's a surprise NOT
Debug.Print ActiveSheet.ChartObjects
_(ActiveSheet.ChartObjects.Count).BottomRightCell.Address: '->CORRECT
ADDRESS
 
You need the 'Set' keyword since you are working with an object
type variable.

Set rngTopLeft = _
ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).TopLeftCell--Cordially,Chip PearsonMicrosoft MVP - ExcelPearson Software Consulting, LLCwww.cpearson.com"Matthew Dodds" <[email protected]> wrote in messageFolks,>> This one has me stumped; can an expert ride to the rescueplease!!>> In the code below the assignment marked **** doesn't work;rngTopLeft> remains resolutely NOTHING in the locals window. In an attemptto> trouble shoot I've added various debug statements to seewhether the> information demanded by the assignment is available, and allprint the> expected info to immediate window.>> Suggestions, please, as to why the assignment to rngTopLeftdoesn't> happen>> Many thanks in anticipation>> Matt>>> On Error Resume Next 'currClust not yet assigned,n'oubliez pas> If Not rngCluster = rngCurrentCluster Then 'change H & Voffset> for charts in new cluster> If iChart = 1 Then 'chart was the very firstchart> iHoffset = iHoffset + iXsize> iVoffset = 0> iChart = iChart + 1> **** rngTopLeft =>ActiveSheet.ChartObjects(ActiveSheet.ChartObjects.Count).TopLeftCell>> Debug.Print ActiveSheet.Name: '->SHEET CORRECT> Debug.Print ActiveSheet.ChartObjects.Count: '->NUMBER CORRECT(1)> Debug.Print ActiveSheet.ChartObjects> _(ActiveSheet.ChartObjects.Count).TopLeftCell.Address:'->CORRECT> ADDRESS> Debug.Print TypeName(ActiveSheet.ChartObjects> _(ActiveSheet.ChartObjects.Count).TopLeftCell): '-> it's arange, now> there's a surprise NOT> Debug.Print ActiveSheet.ChartObjects> _(ActiveSheet.ChartObjects.Count).BottomRightCell.Address:'->CORRECT> ADDRESS>
 
Thanks Chip. And apologies to the group. It was a long day and the tyro
in me was in the ascendant.
 
Back
Top