Charting - Cells and Range - Error 1004

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,

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,
>
>
>