Charting unknown territory

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
735
Hi, I'm new to using charts and macros together. I wrote the code below to enable the chart I am displaying only to use that block of data which is appropriate (it looks for both col A and col B being zero to define its boundaries) but the code stops at the red line, "Application defined or object defined error". I have done a fair bit of tinkering with it, but still no joy. What am I doing wrong, please? Thanks for your help.
Nigel
Code:
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range, y As Integer
    y = 0
    Do
        If [a1].Offset(y) = 0 And [b1].Offset(y) = 0 Then Exit Do
        y = y + 1
    Loop
     Set r = [b24].Offset(y - 1)
    Set r = Range("a25", r)
   [COLOR="Red"] ChartObjects(1).Chart.SetSourceData Source:=Sheets("Graph").Range(r), _
                             PlotBy:=xlColumns[/COLOR]
End Sub

By the way, discovered the [a1] method of entering ranges recently. Why wasn't I told about this before? Much quicker than Range("a1"). However, what is the [] equivalent of Range("a25", r), please? Because it doesn't work if you type [[a25],r], as I would expect. Thanks.
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Is it this that causes the error?

Code:
ChartObjects(1).Chart.SetSourceData Source:=Sheets("Graph").Range(r), PlotBy:=xlColumns

Shouldn't it be?

Code:
ChartObjects(1).Chart.SetSourceData Source:=r, PlotBy:=xlColumns
 

nigelandrewfoster

Well-known Member
Joined
May 27, 2009
Messages
735
You are undoubtedly correct, Mr P. I have amended the code as below. Still stops on red line, but this time "Object does not support this property or method."
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range, y As Integer, g As Worksheet, d As Worksheet
    Set d = Worksheets("Data")
    Set g = Worksheets("Graph")
    y = 0
    Do
        If d.[a1].Offset(y) = 0 And d.[b1].Offset(y) = 0 Then Exit Do
        y = y + 1
    Loop
    Set r = d.[b1].Offset(y - 1)
    Set r = d.Range("a1", r)
    g.ChartObjects(1).Chart.SetSourceData Source:=Sheets("Graph").r, _
         PlotBy:=xlColumns
End Sub
 
Last edited by a moderator:

Watch MrExcel Video

Forum statistics

Threads
1,127,530
Messages
5,625,361
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top