I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address

--
HTH...

Jim Thomlinson


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

Caeres,

The way to do this would be to place the following code into the script.

Cells(ActiveCell.Row, ActiveCell.Column)

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

maybe...

dim myRng as range
with activecell
set myrng = .parent.range(.cells(.row,.column).address)
end with

But that seems very convoluted to me. Why can't you just use Activecell?

Caeres wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

--

Dave Peterson

I just saw your other post. Try to stick 1 post to make life easy...

range(ActiveCell, range("N3")).Select

Selects a rectangle from the active cell through N3...

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address

--
HTH...

Jim Thomlinson


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

Here's the end goal. I'm trying to sort a range from the active cell to N3.
Here's the code I've got right now:

ActiveCell.Select
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(ActiveCell.Row, ActiveCell.Column),
Cells(3, 14)), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
.SetRange Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(3,
14))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

and I get an error message:
"Run-time error '1004': The sort reference is not valid. Make sure that
it's within the data you want to sort, and the first Sort By box isn't the
same or blank."

with the ".SetRange" line highlighted in the debugger. Any ideas? Thanks
for the initial help, that was what I was looking for. Just turns out it
wasn't my only problem.

"Jim Thomlinson" wrote:

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address

--
HTH...

Jim Thomlinson


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

Once you have identified the range you need to decide what column(s) you want
to sort on. Do you know?

Tom

"Caeres" wrote:

Here's the end goal. I'm trying to sort a range from the active cell to N3.
Here's the code I've got right now:

ActiveCell.Select
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(ActiveCell.Row, ActiveCell.Column),
Cells(3, 14)), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
.SetRange Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(3,
14))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

and I get an error message:
"Run-time error '1004': The sort reference is not valid. Make sure that
it's within the data you want to sort, and the first Sort By box isn't the
same or blank."

with the ".SetRange" line highlighted in the debugger. Any ideas? Thanks
for the initial help, that was what I was looking for. Just turns out it
wasn't my only problem.

"Jim Thomlinson" wrote:

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address

--
HTH...

Jim Thomlinson


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

Yes, I want to sort based on column A.

"TomPl" wrote:

Once you have identified the range you need to decide what column(s) you want
to sort on. Do you know?

Tom

"Caeres" wrote:

Here's the end goal. I'm trying to sort a range from the active cell to N3.
Here's the code I've got right now:

ActiveCell.Select
With ActiveWorkbook.Worksheets(ActiveSheet.Name).Sort
.SortFields.Clear
.SortFields.Add Key:=Range(Cells(ActiveCell.Row, ActiveCell.Column),
Cells(3, 14)), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
.SetRange Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(3,
14))
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

and I get an error message:
"Run-time error '1004': The sort reference is not valid. Make sure that
it's within the data you want to sort, and the first Sort By box isn't the
same or blank."

with the ".SetRange" line highlighted in the debugger. Any ideas? Thanks
for the initial help, that was what I was looking for. Just turns out it
wasn't my only problem.

"Jim Thomlinson" wrote:

Your request does not really make a lot of sense??? The active cell is a
single cell on the active sheet. Are you trying to define a range based off
of the active cell???

MsgBox Cells(ActiveCell.Row, ActiveCell.Column).Address
MsgBox Cells(ActiveCell.Row + 5, ActiveCell.Column).Address
MsgBox ActiveCell.Offset(5, 0).Address
MsgBox ActiveCell.Resize(6, 1).Address

--
HTH...

Jim Thomlinson


"Caeres" wrote:

I need to refer to the active cell using the Cells property (eg
Range.Cells(...)). How do I do this?

I'm not sure the point to all of this, but the following should do what you
have described. This will sort the data in columns A thru N based on the
value in column A. Only rows 4 thru the row of the active cell will be
sorted.

Sub SortSelection()

Dim rngCell As String

rngCell = ActiveCell.Row

ActiveSheet.Range("A3:N" & rngCell).Sort Key1:=Range("a4"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Range("A" & rngCell).Select

End Sub

Hope'n this works.

Tom

Had a few problems with your code, but you answered my question. I was
inputting the key value wrong. Thanks for the help.

"TomPl" wrote:

I'm not sure the point to all of this, but the following should do what you
have described. This will sort the data in columns A thru N based on the
value in column A. Only rows 4 thru the row of the active cell will be
sorted.

Sub SortSelection()

Dim rngCell As String

rngCell = ActiveCell.Row

ActiveSheet.Range("A3:N" & rngCell).Sort Key1:=Range("a4"),
Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Range("A" & rngCell).Select

End Sub

Hope'n this works.

Tom