building a range for SetSourceData

[cate]

I am trying to build a range object to feed a chart input. I want to
move from old to new:


' OLD
myChart.SetSourceData Source:=Sheets("Main Sheet").Range( _
"DF71:IS71,DF73:IS73"), PlotBy:=xlRows

' NEW
myChart.SetSourceData Source:=ws , PlotBy:=xlRows



Here is the way I built the robj. I use the other range objects to
collect row and column info. What am I doing wrong?

Dim ws As Range
Set ws = myWs.Range( _
myWs.Range(myWs.Cells(Date_Week.row, FirstDataCol.Column),
myWs.Cells(Date_Week.row, LastDataCol.Column)), _
myWs.Range(myWs.Cells(Pool_Sum.row, FirstDataCol.Column),
myWs.Cells(Pool_Sum.row, LastDataCol.Column)))


The chart doesn't die, but the results are a mess.
Thank you

(I have verified in debug mode that the row and column numbers
returned by all ranges are correct)

 

[JLGWhiz]

Since your range is correct, have you checked to make sure your data types
are the same in the range? i.e. string vs numeric?

 

[cate]

They are not the same type.... I am beginning to wonder if this range
I built is being viewed as an area v/s two separate rows as in the
'Old' construct. Maybe the question should be, how do you build a
range object with multiple rows?

 

[cate]

Set ws = aSheet.Range("DF71:IS71, DF73:IS73")

works just fine... so it's the way I build the range object. I've got

Set ws = aSheet.Range(range, range), and I want
aSheet.Range("range, range") -- or ("string address, string address") -
or

How do you do that?

 

[cate]

ah hahah.. and the answer is! You build each row range object, then
join them with union()

Set ws1 = aSheet.Range(aSheet.Cells(Date_Week.row,
FirstDataCol.Column), aSheet.Cells(Date_Week.row, LastDataCol.Column))
Set ws2 = aSheet.Range(aSheet.Cells(Pool_Sum.row,
FirstDataCol.Column), aSheet.Cells(Pool_Sum.row, LastDataCol.Column))

Set ws = Union(wsDataSource1, wsDataSource2)

Works great!

 

[JLGWhiz]

Perseverence pays off!


ah hahah.. and the answer is! You build each row range object, then
join them with union()

Set ws1 = aSheet.Range(aSheet.Cells(Date_Week.row,
FirstDataCol.Column), aSheet.Cells(Date_Week.row, LastDataCol.Column))
Set ws2 = aSheet.Range(aSheet.Cells(Pool_Sum.row,
FirstDataCol.Column), aSheet.Cells(Pool_Sum.row, LastDataCol.Column))

Set ws = Union(wsDataSource1, wsDataSource2)

Works great!

 

[Chip Pearson]

Perseverence pays off!

So does a spell checker.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[JLGWhiz]

Chip, if that was my worst mistake, I would be very happy!