View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Rick Rothstein \(MVP - VB\)[_2308_] Rick Rothstein \(MVP - VB\)[_2308_] is offline
external usenet poster
 
Posts: 1
Default Address of selected range

The Address property is a String, so you can't Set it to a Range
object. I guess technically you could do this...

Set chrrng = Range(Selection.Address)

but, since the Selection is already a Range, why not just do this...

Set chrrng = Selection


Thank you for your reply. I tried the following:

Sub xprobchart()
sheetnam = "Distribution 10"
icount = 21
col = 10
Dim chrtrng2 As String
Sheets(sheetnam).Select
Range("j2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set chrtrng2 = Selection
MsgBox " chrtrng " & chrtrng2


and got a compile error, objective required, for the line:
set chrrng2 = selection


The reason for your error is that you declared chrtrng2 as a String... you
cannot use Set to assign something to a String... it only works with
objects. Try declaring it this way...

Dim chrtrng2 As Range

then the Set statement will work; but you will have to change your MsgBox
statement to this...

MsgBox "chrtrng " & chrtrng2.Address

because chrtrng2 is now a Range, so you need to reference one of its
properties (I assumed Address) if you want to concatenate it with something.

Rick