I have a table in excel 2007 and i want to get a chart

Question

I have a table in excel 2007 and i want to get a chart.
Here is my code:

Range("A2:P15").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'list'!$A$2:$P$15")
ActiveChart.ChartType = xlLineStacked
ActiveChart.SetSourceData

The beginning of the table is "A2" but the end is not the same every time.
I can't find a solution so please help me.
Thank you ;)

`but the end is not the same every time` - how it changed? maybe you need to [determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) for getting address of your range or [CurrentRegion](http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.currentregion.aspx)? — Dmitry Pavliv, Apr 04 '14 at 12:24
The macro I am programming is for different sheets. On every sheets there is one table starting at cell "A2". But not every table has the same size. Some tables ends with "O14, P15, Q16, etc — user3496832, Apr 04 '14 at 12:37
what do you expect, someone should guess _the logic_ according to which on first sheet table has address `A$2:$P$15`, on second sheet of `A$2:$O$15` and so on, or what? How someone can help you if you don't tell us anything!? — Dmitry Pavliv, Apr 04 '14 at 12:43
Sry it is my first time looking for answers in the internet. I try to express it again. My code now is for a table with the range A2:P15 . But i want to have something like variable's instead of the "A2:P15". do you understand what I mean – — user3496832, Apr 04 '14 at 13:01

score 1 · Accepted Answer · answered Apr 04 '14 at 13:18

If you want to select all of the cells in a sheet from a specific start point (set to A2 in my code below) then I usually use a LastRow and LastCol function for this. There are lots of different ways to get the last row / column, I have included what I normally use.

I have also added to your code and put this inside a procedure named Main. This loops through each sheet in the workbook and gets the range as described above and then goes into the code that you posted. As I do not have the data in your sheets I have not been able to test the code for the charts itself:

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

Let me know if you have any questions about how the code works etc.

@user3496832 No problem, please could you mark this as the accepted answer if it has helped resolve the issue for you. — ChrisProsser, Apr 04 '14 at 13:35
`LastRow = Sheets(pSheet).UsedRange.Rows.Count` - may be interesting: [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) — Dmitry Pavliv, Apr 04 '14 at 13:44
@simoco Interesting point. I have used this a number of times and not had an issue with it, but I appreciate this it may not be appropriate for all scenarios. — ChrisProsser, Apr 04 '14 at 13:50

I have a table in excel 2007 and i want to get a chart

1 Answers1