Sorting by required column

[plasmar8]

Hi all,

I'm trying to write a simple prgramme that will be able to sort the table according the column number that is entered into an input box. i'm trying this but it crashes every time:

Sub Macro1()
Dim n As Integer
Dim mycell As Range
n = InputBox("Please enter which column you would like to sort by")
Set mycell = ActiveCell.Offset(0, n)

With mycell
Selection.CurrentRegion.Select
Selection.Sort Key1:=Range("mycell"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub

any ideas?

 

[SkipVought]

Hi,

Stay away from ActiveANYTHING, Select, Selection.

n = InputBox("Please enter which column you would like to sort by")
Set mycell = [i]TopLeftCellObjectOfTable[/i].Offset(0, n-1)

With mycell
    .CurrentRegion.Sort Key1:=.Cells, Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With
...

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[plasmar8]

Thanks for this Skip, works perfectly
I have to use Activecell cause it needs to be used on a number of tables, but I take your point where possible!

I also noticed that 'range' is taken from the key, and replaced by .cells, is this because? I'm not quite sure how it makes the correlation between mycell and .Cells?

Thank again for your help
Cheers,

 

[SkipVought]

MyCell IS a range. So

with mycell
  .cells
end with

defines the range of mycell which happens to be a one cell range.

If your procedure depends on a selection to define the table range...

n = InputBox("Please enter which column you would like to sort by")
Set mycell = [b]activecell.currentregion.cells(1, n)[/b]

With mycell
    .CurrentRegion.Sort Key1:=.Cells, Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

This does not depend on the user selecting any specific cell within the table; just ANY cell in the table. The answer to the question defines the sort column.

You could bypass the InputBox and go directly to the specific selection...

with selection
    .CurrentRegion.Sort Key1:=.Cells, Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End With

You could trigger the sort using the Worksheet_SelectionChange event...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row = Target.CurrentRegion.Row Then
        test Target
    End If
End Sub
Sub test(rng As Range)
    With rng
        .CurrentRegion.Sort Key1:=.Cells, Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]