How do I return the location (as a range) of ActiveCell?
 

I need the reference to a range to be dynamic, based on the ActiveCell. How
can I return the range of the ActiveCell so that I can use it in operations
requiring a Range as an argument?

How do I return the location (as a range) of ActiveCell?
 

?
ma=activecell.address

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Caeres" wrote in message
...
I need the reference to a range to be dynamic, based on the ActiveCell.
How
can I return the range of the ActiveCell so that I can use it in
operations
requiring a Range as an argument?

How do I return the location (as a range) of ActiveCell?
 

Thanks, just didn't know the syntax. I appreciate it.

How do I return the location (as a range) of ActiveCell?
 

Okay, scratch that, I'm still making some mistake. Here's the code I've got:

MsgBox ActiveCell.Address
Dim ActiveLoc As Range
ActiveLoc = ActiveCell.Address
[ActiveRange:N3].Select
ActiveRange.Activate
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Add
Key:=Range("ActiveRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("D-11 O.F.").Sort
.SetRange Selection.Range
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The MsgBox line returns $A$6, so I know .Address is the right syntax, but
after the message box I get an error message: "Run-time error '91': Object
variable or With block variable not set" and the third line is highlighted in
the debugger. Hovering over it, I get the bubbles "ActiveLoc = Nothing" and
"ActiveCell.Address = $A$6". So something's going wrong with the assignment
of the range value to the variable, but I don't know why. Help?

How do I return the location (as a range) of ActiveCell?
 

Dim ActiveLoc As String

I don't know what the next line does but the above change will get you
there.


Gord Dibben MS Excel MVP

On Mon, 6 Oct 2008 07:43:02 -0700, Caeres
wrote:

Okay, scratch that, I'm still making some mistake. Here's the code I've got:

MsgBox ActiveCell.Address
Dim ActiveLoc As Range
ActiveLoc = ActiveCell.Address
[ActiveRange:N3].Select
ActiveRange.Activate
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("D-11 O.F.").Sort.SortFields.Add
Key:=Range("ActiveRange"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("D-11 O.F.").Sort
.SetRange Selection.Range
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The MsgBox line returns $A$6, so I know .Address is the right syntax, but
after the message box I get an error message: "Run-time error '91': Object
variable or With block variable not set" and the third line is highlighted in
the debugger. Hovering over it, I get the bubbles "ActiveLoc = Nothing" and
"ActiveCell.Address = $A$6". So something's going wrong with the assignment
of the range value to the variable, but I don't know why. Help?