MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Need help with cyntax when using Range to define plot range


Posted by Edward C. on April 10, 2001 11:17 AM

I was given a marco that created a plot over a fixed range and was asked to generalize it work work for any size range. I created a set of Range varables to store the min and max column address over which I want my series in my plot to span. When I check the address in the Range variable ( IE: ARange.Address = $A$503:$A$2353 ) it seems to be fine. However, I am doing something wrong in the way I am using the Range in the argument list for the plot. I tried the statement below both with and without the " "'s but could not get it to work. Could someone tell me what I am doing wrong?
Thanks,
Edward

Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
' ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _
' "A1:A2664,H1:H2664,AV1:AV2664,CH1:CH2664"), PlotBy:=xlColumns

' ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _
' ARange, _
' HRange, _
' AVRange, _
' CHRange), PlotBy:=xlColumns

ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _
"ARange.Address, HRange.Address, AVRange.Address, CHRange.Address"), PlotBy:=xlColumns


Posted by Dave Hawley on April 10, 2001 9:59 PM

ActiveChart.SetSourceData Source:=Sheets("Veh1_NY_20010321_1957").Range( _ "ARange.Address, HRange.Address, AVRange.Address, CHRange.Address"), PlotBy:=xlColumns

Hi Edward

Have you tried it with the range as a relative one:
ARange = Range("A1:A10").Address(RowAbsolute:=False, ColumnAbsolute:=False)


...Or If I have understood you might be able to make use of some dynamic ranges. You can see some examples of these on my Website under "Dynamic Ranges".


Dave

OzGrid Business Applications

Posted by Edward C. on April 11, 2001 12:32 PM

Thanks. I will have a look at your example.
To be honest, I was not even sure if I was using the correct syntax. I did not know if using ARange.Address was the correct thing to do, or if I should be using something else, like ARange.Row. If you have any comments on that I would love to hear them.
Thanks
Edward