How do you get a Selected Range address into a variable?

[Jack]

I am relatively new to Excel programming. I am trying to
generate an Excel chart with different data ranges.
I've been able to select a range of cells for the data
that I want to Chart

--Range(Selection, Selection.End(xlDown)).Select--

I was trying to store the address into a variable to be
used later, i.e.,
--DataRange = Selection.Address--

I was going to use this variable to put into

--ActiveChart.SetSourceData Source:=DataRange _
, PlotBy:=xlColumns

But it generates an error stating that '.Address' is an
invalid property of Selection.

I've been trying all kinds of different objects and
properties for over 3 hours. It seems like a simple thing
to do. But.......Help

Jack

 

[GJones]

Jack;

If I understand you just leave off the .address and use
selection by itself.

Thanks,

Greg

 

[EdgeOfCity]

try this:Source:=range(DataRange)

 

[EdgeOfCity]

or this:
dim DataRange as range
set DataRange=selection 'or else
'or set datarange=rane("...")
....
Source:=DataRange

 

[Guest]

I tried this:

Range(Selection, Selection.End(xlDown)).Select
DataRange = Selection

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range(DataRange) _
, PlotBy:=xlColumns


I got: Method 'Range' of Object '_Global' failed

 

[EdgeOfCity]

<[email protected]> дÈëÓʼþ
DataRange = Selection.address
....
ActiveChart.SetSourceData Source:=Range(datarange) '!!

or
dim DataRange as range
set DataRange=selection
ActiveChart.SetSourceData Source:=datarange '!!
I tried this:

Range(Selection, Selection.End(xlDown)).Select
DataRange = Selection

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range(DataRange) _
, PlotBy:=xlColumns


I got: Method 'Range' of Object '_Global' failed

 

[Guest]

Greg

I tried this:

Range(Selection, Selection.End(xlDown)).Select
DataRange = Selection

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range(DataRange) _
, PlotBy:=xlColumns


I got: Method 'Range' of Object '_Global' failed

Jack

 

[Guest]

Ialso tried this:
I tried this:

Range(Selection, Selection.End(xlDown)).Select
set DataRange = Selection

Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Range(DataRange) _
, PlotBy:=xlColumns


I got: Method 'Range' of Object '_Global' failed at this
last statement.

Jack

 

[Guest]

Hi again.

I tried the first approach:
DataRange = Selection.address
.....
ActiveChart.SetSourceData Source:=Range(datarange) '!!

and I still got the same error.

However, using your 2nd suggestion:
set DataRange=selection
ActiveChart.SetSourceData Source:=datarange '!!
This indeed did work!!!! Glory be!

I don't understand why the 1st approach did not work.

I really appreciate your time and effort in helping me.
You guys that respond to our silly issues in these
discussion groups are really great.

I have never been disappointed.

Thanks

Jack

 

[EdgeOfCity]

sorry,I really don't know how it turns out.
but when i tred below,it worked
Sub test()
Dim add As String
add = Selection.Address 'i select a3:a9
[a1].Select 'change selection to [a1]
Range(add).Select 'select a3:a9 again
End Sub

..(My english is so poor. maybe i didn't describe properly.)
<[email protected]> дÈëÓʼþ
Hi again.

I tried the first approach:
DataRange = Selection.address
.....
ActiveChart.SetSourceData Source:=Range(datarange) '!!

and I still got the same error.

However, using your 2nd suggestion:
set DataRange=selection
ActiveChart.SetSourceData Source:=datarange '!!
This indeed did work!!!! Glory be!

I don't understand why the 1st approach did not work.

I really appreciate your time and effort in helping me.
You guys that respond to our silly issues in these
discussion groups are really great.

I have never been disappointed.

Thanks

Jack