ExcelBanter - View Single Post - Charting - Cells and Range

vbaprog

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 Whe=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

"vbaprog" wrote in message
...
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,