+ Reply to Thread
Results 1 to 4 of 4

Problem with chart creation.

  1. #1
    daniel chen
    Guest

    Problem with chart creation.

    Sheet1 was populated with column arrays in Range("A8:J50").
    I wanted to creat a chart for column array as specified in cells A1, A2, A3
    as rowbegin, rowend,and columnofinterest respectively.
    Soon after the chart was created all the values in Cells(*, *) were lost,
    and the macro ceased to function properly.
    Can you help me? Thanks

    Sub Creat_a_chart()
    Dim rowbegin, rowend, columnofinterest As Double
    Cells(1, 1) = 8 ' as given
    Cells(2, 1) = 20 ' as given
    Cells(3, 1) = 3 ' as given
    rowbegin = Cells(1, 1)
    rowend = Cells(2, 1)
    columnofinterest = Cells(3, 1)
    Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    columnofinterest)).Select
    Charts.Add
    ' From this point on, all the Cells(*, *) failed _ stating "Method
    'Cells' of object '_Global' failed "

    ActiveChart.ChartType = xlLineMarkers
    On Error Resume Next
    ActiveChart.SetSourceData Source:= _
    Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    Cells(rowend, columnofinterest)), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveChart.HasLegend = False
    Cells(1, 2) = Cells(1, 1).value ' value lost
    Cells(2, 2) = Cells(2, 1).value ' value lost
    Cells(3, 2) = Cells(3, 1).value ' value lost
    End Sub



  2. #2
    Ed Ferrero
    Guest

    Re: Problem with chart creation.

    Hi daniel,

    You are almost there, just need to define rowbegin, rowend, and
    columnofinterest as the row and column numbers - not the contents of the
    cells as in your code.

    See amended code...

    Sub Creat_a_chart()
    Dim rowbegin, rowend, columnofinterest As Double

    Cells(1, 1) = 8 ' as given
    Cells(2, 1) = 20 ' as given
    Cells(3, 1) = 3 ' as given

    ' these are the values in cells(x,y)
    ' rowbegin = Cells(1, 1)
    ' rowend = Cells(2, 1)
    ' columnofinterest = Cells(3, 1)

    ' you need to set row and column number...
    rowbegin = 1
    rowend = 3
    columnofinterest = 1

    Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    columnofinterest)).Select
    Charts.Add

    ' From this point on, all the Cells(*, *) failed _ stating "Method
    'Cells' of object '_Global' failed "

    ActiveChart.ChartType = xlLineMarkers
    On Error Resume Next
    ActiveChart.SetSourceData Source:= _
    Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    Cells(rowend, columnofinterest)), _
    PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    ActiveChart.HasLegend = False

    ' also here you need to activate the sheet again, because the chart
    ' is active at this point and Cells(1,1) won't work
    Sheets(1).Activate
    Cells(1, 2) = Cells(1, 1).Value ' value lost
    Cells(2, 2) = Cells(2, 1).Value ' value lost
    Cells(3, 2) = Cells(3, 1).Value ' value lost
    End Sub

    You might consider the following way of doing the same thing.
    This gives you somewhat better control of how the chart is positioned.

    Sub BetterWay()
    Dim rngSeries As Range
    Dim oCht As ChartObject
    Dim strTitle As String

    Cells(1, 1) = 8
    Cells(2, 1) = 20
    Cells(3, 1) = 3

    strTitle = "My New Chart"

    ' set the range to chart with a simple statement
    Set rngSeries = Worksheets("Sheet1").Range("A1:A3")
    ' or use End(xlDown) to set a range dynamically
    ' With Worksheets("Sheet1")
    ' Set rngSeries = .Range("A1", .Range("A1").End(xlDown))
    ' End With

    ' add a chart object to the worksheet - this method gives you
    ' control over where the chart object is placed and its size
    Set oCht = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)

    ' then use the chart wizard method of the chart to add a series
    ' note that oCht is the chart object and oCht.Chart is the chart
    ' contained by that object
    oCht.Chart.ChartWizard Source:=rngSeries, gallery:=xlLine, Title:=strTitle

    End Sub

    Ed Ferrero
    Microsoft Excel MVP
    http://www.edferrero.com


    > Sheet1 was populated with column arrays in Range("A8:J50").
    > I wanted to creat a chart for column array as specified in cells A1, A2,
    > A3 as rowbegin, rowend,and columnofinterest respectively.
    > Soon after the chart was created all the values in Cells(*, *) were lost,
    > and the macro ceased to function properly.
    > Can you help me? Thanks
    >
    > Sub Creat_a_chart()
    > Dim rowbegin, rowend, columnofinterest As Double
    > Cells(1, 1) = 8 ' as given
    > Cells(2, 1) = 20 ' as given
    > Cells(3, 1) = 3 ' as given
    > rowbegin = Cells(1, 1)
    > rowend = Cells(2, 1)
    > columnofinterest = Cells(3, 1)
    > Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    > columnofinterest)).Select
    > Charts.Add
    > ' From this point on, all the Cells(*, *) failed _ stating "Method
    > 'Cells' of object '_Global' failed "
    >
    > ActiveChart.ChartType = xlLineMarkers
    > On Error Resume Next
    > ActiveChart.SetSourceData Source:= _
    > Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    > Cells(rowend, columnofinterest)), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    > ActiveChart.HasLegend = False
    > Cells(1, 2) = Cells(1, 1).value ' value lost
    > Cells(2, 2) = Cells(2, 1).value ' value lost
    > Cells(3, 2) = Cells(3, 1).value ' value lost
    > End Sub
    >




  3. #3
    daniel chen
    Guest

    Re: Problem with chart creation.

    Hi Ed,
    First I thank you sincerely.
    I got your idea and my final product is_

    Sub FinalWay()
    Dim rngSeries As Range
    Dim oCht As ChartObject
    Dim strTitle As String
    Dim rowbegin, rowend, columnofinterest As Long
    rowbegin = InputBox(prompt:=" Enter the beginning row")
    rowend = InputBox(prompt:=" Enter the ending row")
    columnofinterest = InputBox(prompt:=" Ender the column of interest")
    Cells(1, 1) = rowbegin
    Cells(2, 1) = rowend
    Cells(3, 1) = columnofinterest
    strTitle = "My New Chart"
    Set rngSeries = Worksheets("Sheet1").Range(Cells(rowbegin, _
    columnofinterest), Cells(rowend, columnofinterest))
    Set oCht = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)
    oCht.Chart.ChartWizard Source:=rngSeries, gallery:=xlLine, Title:=strTitle
    oCht.Chart.HasLegend = False
    End Sub


    "Ed Ferrero" <[email protected]> wrote in message
    news:%[email protected]...
    > Hi daniel,
    >
    > You are almost there, just need to define rowbegin, rowend, and
    > columnofinterest as the row and column numbers - not the contents of the
    > cells as in your code.
    >
    > See amended code...
    >
    > Sub Creat_a_chart()
    > Dim rowbegin, rowend, columnofinterest As Double
    >
    > Cells(1, 1) = 8 ' as given
    > Cells(2, 1) = 20 ' as given
    > Cells(3, 1) = 3 ' as given
    >
    > ' these are the values in cells(x,y)
    > ' rowbegin = Cells(1, 1)
    > ' rowend = Cells(2, 1)
    > ' columnofinterest = Cells(3, 1)
    >
    > ' you need to set row and column number...
    > rowbegin = 1
    > rowend = 3
    > columnofinterest = 1
    >
    > Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    > columnofinterest)).Select
    > Charts.Add
    >
    > ' From this point on, all the Cells(*, *) failed _ stating "Method
    > 'Cells' of object '_Global' failed "
    >
    > ActiveChart.ChartType = xlLineMarkers
    > On Error Resume Next
    > ActiveChart.SetSourceData Source:= _
    > Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    > Cells(rowend, columnofinterest)), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    > ActiveChart.HasLegend = False
    >
    > ' also here you need to activate the sheet again, because the chart
    > ' is active at this point and Cells(1,1) won't work
    > Sheets(1).Activate
    > Cells(1, 2) = Cells(1, 1).Value ' value lost
    > Cells(2, 2) = Cells(2, 1).Value ' value lost
    > Cells(3, 2) = Cells(3, 1).Value ' value lost
    > End Sub
    >
    > You might consider the following way of doing the same thing.
    > This gives you somewhat better control of how the chart is positioned.
    >
    > Sub BetterWay()
    > Dim rngSeries As Range
    > Dim oCht As ChartObject
    > Dim strTitle As String
    >
    > Cells(1, 1) = 8
    > Cells(2, 1) = 20
    > Cells(3, 1) = 3
    >
    > strTitle = "My New Chart"
    >
    > ' set the range to chart with a simple statement
    > Set rngSeries = Worksheets("Sheet1").Range("A1:A3")
    > ' or use End(xlDown) to set a range dynamically
    > ' With Worksheets("Sheet1")
    > ' Set rngSeries = .Range("A1", .Range("A1").End(xlDown))
    > ' End With
    >
    > ' add a chart object to the worksheet - this method gives you
    > ' control over where the chart object is placed and its size
    > Set oCht = ActiveSheet.ChartObjects.Add(100, 30, 400, 250)
    >
    > ' then use the chart wizard method of the chart to add a series
    > ' note that oCht is the chart object and oCht.Chart is the chart
    > ' contained by that object
    > oCht.Chart.ChartWizard Source:=rngSeries, gallery:=xlLine,
    > Title:=strTitle
    >
    > End Sub
    >
    > Ed Ferrero
    > Microsoft Excel MVP
    > http://www.edferrero.com
    >
    >
    >> Sheet1 was populated with column arrays in Range("A8:J50").
    >> I wanted to creat a chart for column array as specified in cells A1, A2,
    >> A3 as rowbegin, rowend,and columnofinterest respectively.
    >> Soon after the chart was created all the values in Cells(*, *) were lost,
    >> and the macro ceased to function properly.
    >> Can you help me? Thanks
    >>
    >> Sub Creat_a_chart()
    >> Dim rowbegin, rowend, columnofinterest As Double
    >> Cells(1, 1) = 8 ' as given
    >> Cells(2, 1) = 20 ' as given
    >> Cells(3, 1) = 3 ' as given
    >> rowbegin = Cells(1, 1)
    >> rowend = Cells(2, 1)
    >> columnofinterest = Cells(3, 1)
    >> Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    >> columnofinterest)).Select
    >> Charts.Add
    >> ' From this point on, all the Cells(*, *) failed _ stating "Method
    >> 'Cells' of object '_Global' failed "
    >>
    >> ActiveChart.ChartType = xlLineMarkers
    >> On Error Resume Next
    >> ActiveChart.SetSourceData Source:= _
    >> Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    >> Cells(rowend, columnofinterest)), _
    >> PlotBy:=xlColumns
    >> ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    >> ActiveChart.HasLegend = False
    >> Cells(1, 2) = Cells(1, 1).value ' value lost
    >> Cells(2, 2) = Cells(2, 1).value ' value lost
    >> Cells(3, 2) = Cells(3, 1).value ' value lost
    >> End Sub
    >>

    >
    >




  4. #4
    Tushar Mehta
    Guest

    Re: Problem with chart creation.

    You can actually do this without VBA. See examples 3 and 4 in
    Dynamic Charts
    http://www.tushar-mehta.com/excel/ne...rts/index.html

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <uk0wf.235255$qk4.212223@bgtnsc05-
    news.ops.worldnet.att.net>, [email protected] says...
    > Sheet1 was populated with column arrays in Range("A8:J50").
    > I wanted to creat a chart for column array as specified in cells A1, A2, A3
    > as rowbegin, rowend,and columnofinterest respectively.
    > Soon after the chart was created all the values in Cells(*, *) were lost,
    > and the macro ceased to function properly.
    > Can you help me? Thanks
    >
    > Sub Creat_a_chart()
    > Dim rowbegin, rowend, columnofinterest As Double
    > Cells(1, 1) = 8 ' as given
    > Cells(2, 1) = 20 ' as given
    > Cells(3, 1) = 3 ' as given
    > rowbegin = Cells(1, 1)
    > rowend = Cells(2, 1)
    > columnofinterest = Cells(3, 1)
    > Range(Cells(rowbegin, columnofinterest), Cells(rowend,
    > columnofinterest)).Select
    > Charts.Add
    > ' From this point on, all the Cells(*, *) failed _ stating "Method
    > 'Cells' of object '_Global' failed "
    >
    > ActiveChart.ChartType = xlLineMarkers
    > On Error Resume Next
    > ActiveChart.SetSourceData Source:= _
    > Sheets("Sheet1").Range(Cells(rowbegin, columnofinterest),
    > Cells(rowend, columnofinterest)), _
    > PlotBy:=xlColumns
    > ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
    > ActiveChart.HasLegend = False
    > Cells(1, 2) = Cells(1, 1).value ' value lost
    > Cells(2, 2) = Cells(2, 1).value ' value lost
    > Cells(3, 2) = Cells(3, 1).value ' value lost
    > End Sub
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1