+ Reply to Thread
Results 1 to 3 of 3

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?

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

    Cells(1, 1) = 8 ' as given example
    Cells(2, 1) = 20 ' as given example
    Cells(3, 1) = 3 ' as given example
    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 lost
    Cells(2, 2) = Cells(2, 1) ' value lost
    Cells(3, 2) = Cells(3, 1) ' value lost
    End Sub



  2. #2
    Jim Cone
    Guest

    Re: Problem with chart creation

    daniel,

    "Charts.Add" creates a new chart sheet, which becomes the active sheet.
    "Cells", with no sheet qualifier, refers to the active sheet.
    There are no cells on a chart sheet.

    '-----------------
    Sub Create_a_chart()
    Dim rowBegin as Long
    Dim rowEnd as Long
    Dim columnOfInterest As Long
    Dim objSheet as Excel.Worksheet

    Set objSheet = ActiveSheet
    objSheet.Cells(1, 1).Value = 8
    'etc
    '--------------------------

    Regards,
    Jim Cone
    San Francisco, USA
    http://www.realezsites.com/bus/primitivesoftware


    "daniel chen" <danchen@worldnet.att.net> wrote in message news:aB2wf.235594$qk4.36931@bgtnsc05-news.ops.worldnet.att.net...
    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?

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

    Cells(1, 1) = 8 ' as given example
    Cells(2, 1) = 20 ' as given example
    Cells(3, 1) = 3 ' as given example
    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 lost
    Cells(2, 2) = Cells(2, 1) ' value lost
    Cells(3, 2) = Cells(3, 1) ' value lost
    End Sub



  3. #3
    daniel chen
    Guest

    Re: Problem with chart creation

    Hi Jim,
    Thanks for the explanation. I have it working.

    "Jim Cone" <jim.coneXXX@rcn.comXXX> wrote in message
    news:uFsjoQGFGHA.3468@TK2MSFTNGP15.phx.gbl...
    > daniel,
    >
    > "Charts.Add" creates a new chart sheet, which becomes the active sheet.
    > "Cells", with no sheet qualifier, refers to the active sheet.
    > There are no cells on a chart sheet.
    >
    > '-----------------
    > Sub Create_a_chart()
    > Dim rowBegin as Long
    > Dim rowEnd as Long
    > Dim columnOfInterest As Long
    > Dim objSheet as Excel.Worksheet
    >
    > Set objSheet = ActiveSheet
    > objSheet.Cells(1, 1).Value = 8
    > 'etc
    > '--------------------------
    >
    > Regards,
    > Jim Cone
    > San Francisco, USA
    > http://www.realezsites.com/bus/primitivesoftware
    >
    >
    > "daniel chen" <danchen@worldnet.att.net> wrote in message
    > news:aB2wf.235594$qk4.36931@bgtnsc05-news.ops.worldnet.att.net...
    > 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?
    >
    > Sub Creat_a_chart()
    > Dim rowbegin, rowend, columnofinterest As Double
    >
    > Cells(1, 1) = 8 ' as given example
    > Cells(2, 1) = 20 ' as given example
    > Cells(3, 1) = 3 ' as given example
    > 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 lost
    > Cells(2, 2) = Cells(2, 1) ' value lost
    > Cells(3, 2) = Cells(3, 1) ' 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