Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Placing shapes exactly on charts. | Charts and Charting in Excel | |||
Need help with a tricky problem of placing a number within an array... | Excel Worksheet Functions | |||
Placing values in a range on a non-active sheet | Excel Programming | |||
Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions | |||
Reference range in formula problem | Excel Programming |