请教：如何用代码移动chat

zhangjw8479

本人用代码add插入一图表，但如何用代码把他移到指定的位置，如A8：H30，该如何写代码？谢谢

snood

录制宏

zhangjw8479

Sub DrawChart()
' Keyboard Shortcut: Ctrl+d
'
    Dim myArea As Object
    Dim n As Integer
    On Error Resume Next
  
    n = Range("c61").Value
    n = n + 64
   
    ActiveSheet.ChartObjects.Delete '删除工作表上所有的ChartObject对象
 
    If n > 79 Then
        MsgBox "you input a number bigger than 15,system will use the 15 as step"
        n = 79
    End If
   
    Set myArea = Worksheets("ppk").Range(Cells(65, 10), Cells(n, 10))
   
    Set myChart = Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=myArea, PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).XValues = "=PpK!R65C8:R79C8"
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Ppk"
    ActiveChart.HasLegend = False
    
    
        
        
    ,此处加入移动图表的代码，将上面动态创建的图表移动到本页指定的位置

End Sub

willin2000

ActiveWindow.Visible = False

Sheets("ppk").ChartObjects(1).Top = [A8].Top
Sheets("ppk").ChartObjects(1).Left = [A8].Left

Sheets("ppk").ChartObjects(1).Width = Range("A8:H20").Width
Sheets("ppk").ChartObjects(1).Height = Range("A8:H20").Height

zhangjw8479

谢谢4楼，已搞定

willin2000

可以简化些:

Sub DrawChart()
' Keyboard Shortcut: Ctrl+d
'
Dim myArea As Object
Dim n As Integer
Dim ch As ChartObject
Dim ws As Worksheet
  
Set ws = Worksheets("ppk")
For Each ch In ws.ChartObjects
If Not (Intersect(ws.Range("A8:H20"), ch.TopLeftCell) Is Nothing) Then ch.Delete
Next ch

n = Range("c61").Value
If n > 79 Then
    MsgBox "you input a number bigger than 15,system will use the 15 as step"
    n = 79
End If

Set ch = ws.ChartObjects.Add([A8].Left, [A8].Top, Range("A8:H20").Width, Range("A8:H20").Height)

Set myArea = Worksheets("ppk").Range("K65").Resize(n, 1)
With ch.Chart
.ChartType = xlColumnClustered
.SetSourceData Source:=myArea, PlotBy:=xlColumns
.SeriesCollection(1).XValues = myArea.Offset(0, -1)
.Location Where:=xlLocationAsObject, Name:="ppk"
.HasLegend = False
End With

End Sub

[此贴子已经被作者于2007-5-29 13:08:11编辑过]