我在excel 2007中有一张桌子,我想要一张图表。
这是我的代码:
Range("A2:P15").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'list'!$A$2:$P$15")
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData
该表的开头是“ A2”,但每次结尾都不相同。
我找不到解决方案,请帮助我。
谢谢 ;)
如果要从特定起点(在下面的代码中设置为A2)选择工作表中的所有单元格,则通常使用LastRow和LastCol函数。有很多不同的方法来获取最后一行/列,我已经包括了我通常使用的内容。
我还添加了您的代码,并将其放在名为Main的过程中。这将遍历工作簿中的每个工作表,并如上所述获得范围,然后进入您发布的代码。由于您的工作表中没有数据,因此我无法测试图表本身的代码:
Sub main()
Const cStartCell As String = "A2"
Dim vLastRow As Long, vLastCol As String
Dim vRange As Range
Dim vSheet As Worksheet
For Each vSheet In Application.ActiveWorkbook.Sheets
vLastRow = LastRow(vSheet.Name)
vLastCol = LastCol(vSheet.Name)
Set vRange = vSheet.Range(cStartCell & ":" & vLastCol & vLastRow)
Debug.Print "Sheet " & vSheet.Name & ", vRange set to address range: " & vRange.Address()
vSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range(vRange)
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData (vRange)
Next vSheet
End Sub
Function LastRow(pSheet As String)
On Error GoTo LastRow_err
LastRow = Sheets(pSheet).UsedRange.Rows.Count
LastRow_exit:
Exit Function
LastRow_err:
Debug.Print "Error in LastRow function", vbCrLf, "Err no: ", _
Err.Number, vbCrLf, "Err Description: ", Err.Description
End Function
Function LastCol(pSheet As String)
On Error GoTo LastCol_err
LastCol = ColNoToLetter(Sheets(pSheet).UsedRange.Columns.Count)
LastCol_exit:
Exit Function
LastCol_err:
Debug.Print "Error in LastCol function", vbCrLf, "Err no: ", _
Err.Number, vbCrLf, "Err Description: ", Err.Description
End Function
Function ColNoToLetter(pColNo As Integer)
On Error GoTo ColNoToLetter_err
' Goes from currently active cell and finds the next available row
' Local constants / variables
Const cProcName = "ColNoToLetter"
Dim vNumberOne As Integer
Dim vNumberTwo As Integer
Dim vLetterOne As String
Dim vLetterTwo As String
vNumberOne = 0
vNumberTwo = 0
vLetterOne = Empty
vLetterTwo = Empty
vNumberOne = Int((pColNo - 1) / 26)
vNumberTwo = pColNo - (vNumberOne * 26)
vLetterTwo = Chr(vNumberTwo + 64)
If vNumberOne >= 1 Then
vLetterOne = Chr(vNumberOne + 64)
End If
ColNoToLetter = vLetterOne & vLetterTwo
ColNoToLetter_exit:
Exit Function
ColNoToLetter_err:
Debug.Print "Error in ColNoToLetter function", vbCrLf, "Err no: ", _
Err.Number, vbCrLf, "Err Description: ", Err.Description
Resume Next
End Function
如果您对代码的工作方式有任何疑问,请告诉我。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句