Range reference problem placing charts
 

I need to place multiple charts at specifice locations in a worksheet.
The chart locations depend on the column headers of the source data.
My problem is that the range statement does not allow for variable cell
references.
I have tried several variations of Range. The only reference that works is
the format "LetterNumber" as in Range("A1")
Range(Cells(x,y)) does not work
Range(Cells(x,y),Cells(x,y)) does not work
Range("A" & x) does not work
var1="a" & str(x)
Range(var1) does not work.
I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate
Set ChtObj = ActiveSheet.ChartObjects("MyChart")


ChtObj.Top = Range(cells(p,q)).Top
ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop

Range reference problem placing charts
 

Try:

Dim mc as range
Set mc = ActiveSheet.Cells(p, q)
ChtObj.Top = Range(mc.Address).Top
ChtObj.Left = Range(mc.Address).Left

"Henri" wrote:

I need to place multiple charts at specifice locations in a worksheet.
The chart locations depend on the column headers of the source data.
My problem is that the range statement does not allow for variable cell
references.
I have tried several variations of Range. The only reference that works is
the format "LetterNumber" as in Range("A1")
Range(Cells(x,y)) does not work
Range(Cells(x,y),Cells(x,y)) does not work
Range("A" & x) does not work
var1="a" & str(x)
Range(var1) does not work.
I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate
Set ChtObj = ActiveSheet.ChartObjects("MyChart")


ChtObj.Top = Range(cells(p,q)).Top
ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop

Range reference problem placing charts
 

Thanks Toppers!
That worked great.
I have still one other problem. As I generate the next chart, I am not able
to select and place it in the next range. All new charts are generated in the
same spot.
How do I select for the next chart?

ActiveChart.Parent.Name = "MyChart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet7.Cells(z, 1) & " " &
Sheet7.Cells(1, y)
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
If countvalues 1 Then
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear,
Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
End If
Sheet8.Activate
Set mc = ActiveSheet.Cells(chartcounter + 10, 1)
Set ChtObj = ActiveSheet.ChartObjects("MyChart")

ChtObj.Select

ChtObj.Top = Range(mc.Address).Top
ChtObj.Left = Range(mc.Address).Left

Loop


"Toppers" wrote:

Try:

Dim mc as range
Set mc = ActiveSheet.Cells(p, q)
ChtObj.Top = Range(mc.Address).Top
ChtObj.Left = Range(mc.Address).Left

"Henri" wrote:

I need to place multiple charts at specifice locations in a worksheet.
The chart locations depend on the column headers of the source data.
My problem is that the range statement does not allow for variable cell
references.
I have tried several variations of Range. The only reference that works is
the format "LetterNumber" as in Range("A1")
Range(Cells(x,y)) does not work
Range(Cells(x,y),Cells(x,y)) does not work
Range("A" & x) does not work
var1="a" & str(x)
Range(var1) does not work.
I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate
Set ChtObj = ActiveSheet.ChartObjects("MyChart")


ChtObj.Top = Range(cells(p,q)).Top
ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop

Range reference problem placing charts
 

try:

For Each chrtobj In ActiveSheet.ChartObjects
..
..
..
..
..


next chrtobj

"Henri" wrote:

Thanks Toppers!
That worked great.
I have still one other problem. As I generate the next chart, I am not able
to select and place it in the next range. All new charts are generated in the
same spot.
How do I select for the next chart?

ActiveChart.Parent.Name = "MyChart"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Sheet7.Cells(z, 1) & " " &
Sheet7.Cells(1, y)
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
If countvalues 1 Then
ActiveChart.SeriesCollection(1).Trendlines.Add(Typ e:=xlLinear,
Forward:=0, _
Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
End If
Sheet8.Activate
Set mc = ActiveSheet.Cells(chartcounter + 10, 1)
Set ChtObj = ActiveSheet.ChartObjects("MyChart")

ChtObj.Select

ChtObj.Top = Range(mc.Address).Top
ChtObj.Left = Range(mc.Address).Left

Loop


"Toppers" wrote:

Try:

Dim mc as range
Set mc = ActiveSheet.Cells(p, q)
ChtObj.Top = Range(mc.Address).Top
ChtObj.Left = Range(mc.Address).Left

"Henri" wrote:

I need to place multiple charts at specifice locations in a worksheet.
The chart locations depend on the column headers of the source data.
My problem is that the range statement does not allow for variable cell
references.
I have tried several variations of Range. The only reference that works is
the format "LetterNumber" as in Range("A1")
Range(Cells(x,y)) does not work
Range(Cells(x,y),Cells(x,y)) does not work
Range("A" & x) does not work
var1="a" & str(x)
Range(var1) does not work.
I need to increment the placement of the top left corner of each graph.

Can anyone help ?

Sheet8.Activate
Set ChtObj = ActiveSheet.ChartObjects("MyChart")


ChtObj.Top = Range(cells(p,q)).Top
ChtObj.Left = Range(cells(p,q)).Left

p=p+20

Loop