Charting - Cells and Range - Error 1004

Hi

Try...

Sub test()
Dim Row1 As Long, Row2 As Long, Col1 As Long, Col2 As Long
Dim r As Range, ws As Worksheet, c As Chart
Set ws = Sheets("Sheet1")
Row1 = 16
Row2 = 19
Col1 = 1
Col2 = 3
With ws
Set r = Union(.Range(.Cells(Row1, Col1), .Cells(Row2, Col1)), _
..Range(.Cells(Row1, Col2), .Cells(Row2, Col2)))
Set c = Charts.Add
c.Location Where:=xlLocationAsObject, Name:=ws.Name
Set c = ActiveChart
c.SetSourceData Source:=r, PlotBy:=xlColumns
c.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
End With
End Sub


--

XL2003
Regards

William
willwest22@yahoo.com


"vbaprog" <vbaprog@discussions.microsoft.com> wrote in message
news:2112B180-6C82-4107-9ECD-C29B8F0EB12A@microsoft.com...
> Hi,
>
> I want to create a chart that is plotting data from two different
> columns. The column names and the length of the columns are determined by
> other data in the program which vary according to the user input.
>
> The last line of the code generates Runtime error 1004:
> Application-defined or object-defined error. The other code with single
> quote
> also generates the same error. What I need is a good replacement for the
> statement below but with the flexibility to define the columns and their
> length programatically.
>
> ActiveChart.SetSourceData
> Source:=Sheets("Sheet1").Range("A16:A19,C16:C19"),
> PlotBy _
> :=xlColumns
>
> The error with my code is with defining the range becoz when I
> place
> the cursor on the cells(row1,col1) in the 8th line (setting range1) this
> is
> what is displayed.
> cells(row1,col1) = <Method 'Cells' of object '_Global' failed>. Any help
> is
> very much appreciated.
>
> My code:
> ----------------------------------------------------------------------------------------------
> Sub ChartCreation()
> Dim row1, row2, col1, col2 As Integer 'Values are determined by other
> data in
> the program
> Dim range1, range2, myRange As Range
> row1 = 16
> row2 = 19
> col1 = 1
> col2 = 3
>
> Set range1 = Range(Cells(row1, col1), Cells(row2, col1))
> Set range2 = Range(Cells(row1, col2), Cells(row2, col2))
> Set myRange = Union(range1, range2)
> Sheet1.Select
> 'Set myRange = Sheets("Sheet1").Range("A16:A19,C16:C19") ' This also
>
>
> generates the
>
> same error
> Charts.Add
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(myRange),
> PlotBy _
> :=xlColumns
> End Sub
> ------------------------------------------------------------------------------------------------
> TIA,

Thanks William

"William" wrote:

> Hi
>
> Try...
>
> Sub test()
> Dim Row1 As Long, Row2 As Long, Col1 As Long, Col2 As Long
> Dim r As Range, ws As Worksheet, c As Chart
> Set ws = Sheets("Sheet1")
> Row1 = 16
> Row2 = 19
> Col1 = 1
> Col2 = 3
> With ws
> Set r = Union(.Range(.Cells(Row1, Col1), .Cells(Row2, Col1)), _
> ..Range(.Cells(Row1, Col2), .Cells(Row2, Col2)))
> Set c = Charts.Add
> c.Location Where:=xlLocationAsObject, Name:=ws.Name
> Set c = ActiveChart
> c.SetSourceData Source:=r, PlotBy:=xlColumns
> c.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
> End With
> End Sub
>
>
> --
>
> XL2003
> Regards
>
> William
> willwest22@yahoo.com
>
>
> "vbaprog" <vbaprog@discussions.microsoft.com> wrote in message
> news:2112B180-6C82-4107-9ECD-C29B8F0EB12A@microsoft.com...
> > Hi,
> >
> > I want to create a chart that is plotting data from two different
> > columns. The column names and the length of the columns are determined by
> > other data in the program which vary according to the user input.
> >
> > The last line of the code generates Runtime error 1004:
> > Application-defined or object-defined error. The other code with single
> > quote
> > also generates the same error. What I need is a good replacement for the
> > statement below but with the flexibility to define the columns and their
> > length programatically.
> >
> > ActiveChart.SetSourceData
> > Source:=Sheets("Sheet1").Range("A16:A19,C16:C19"),
> > PlotBy _
> > :=xlColumns
> >
> > The error with my code is with defining the range becoz when I
> > place
> > the cursor on the cells(row1,col1) in the 8th line (setting range1) this
> > is
> > what is displayed.
> > cells(row1,col1) = <Method 'Cells' of object '_Global' failed>. Any help
> > is
> > very much appreciated.
> >
> > My code:
> > ----------------------------------------------------------------------------------------------
> > Sub ChartCreation()
> > Dim row1, row2, col1, col2 As Integer 'Values are determined by other
> > data in
> > the program
> > Dim range1, range2, myRange As Range
> > row1 = 16
> > row2 = 19
> > col1 = 1
> > col2 = 3
> >
> > Set range1 = Range(Cells(row1, col1), Cells(row2, col1))
> > Set range2 = Range(Cells(row1, col2), Cells(row2, col2))
> > Set myRange = Union(range1, range2)
> > Sheet1.Select
> > 'Set myRange = Sheets("Sheet1").Range("A16:A19,C16:C19") ' This also
> >
> >
> > generates the
> >
> > same error
> > Charts.Add
> > ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(myRange),
> > PlotBy _
> > :=xlColumns
> > End Sub
> > ------------------------------------------------------------------------------------------------
> > TIA,
>
>
>