Charting - Cells and Range - Error 1004

[vbaprog]

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 Su

 

[William]

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
(e-mail address removed)

 

[vbaprog]

Thanks William

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
(e-mail address removed)