Compile Error While Setting Range for Chart Source Data

[SBF12345]

Greetings,

I am receiving a compile error expected: = message after trying to set the source data range for an xyscatter chart type on the line below. I would like to use data ranges from another workbook "COMM_COMBINED.xls" and a worksheet by the worksheet variable name zz.

ch.SetSourceData (Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,b),Cells(yy,b)) , Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,a),Cells(yy,a)))

The above line exists within the context of the below script:

Set ch = Workbooks("COMM_Regress.xls").Worksheets(zz & "_C").Charts.Add

ch.Name = Workbooks("COMM_COMBINED.xls").Worksheet(zz).Cells(2, a).Value & Workbooks("COMM_COMBINED.xls").Worksheet(zz).Cells(2, b).Value
ch.ChartType = xlXYScatter
ch.SetSourceData (Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,b),Cells(yy,b)) , Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,a),Cells(yy,a)))

Thanks!

 

[NdNoviceHlp]

I assume there is some number value for yy as well as a string value for zz? If zz is the name of a worksheet then use "zz" with quotes. Anyways, give this a trial...

Set ch = Workbooks("COMM_Regress.xls").Worksheets(zz).Charts.Add
ch.Name = Workbooks("COMM_COMBINED.xls").Worksheet(zz).Cells(2, "a").Value & Workbooks("COMM_COMBINED.xls").Worksheet(zz).Cells(2, "b").Value
ch.ChartType = xlXYScatter
ch.SetSourceData (Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,"A"),Cells(yy,"A")) , Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,"B"),Cells(yy,"B")))

HTH. Dave

 

[Jon Peltier]

Also, do you want to say:

ch.SetSourceData Union(Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,"A"),Cells(yy,"A")) , Workbooks("COMM_COMBINED.xls").Worksheets(zz).Range(Cells(3,"B"),Cells(yy,"B")))