sort using forms button - cycle thru 9 columns

[mmadden2]

hi all,

i've come across some nifty sorting code and it works well for me (excel
2003). so far i am able to create a forms button and assign it to this macro.
here's how it currently works...

i have data in 9 columns. row 1 is the headers. when you click the button,
data from one of the columns is sorted. perfect. what i would really like to
do is click that single button over and over and have the macro cycle from
column to column, sorting as it goes.

example... click (sort by A1), click (sort by B1), click (sort by C1) and so
on. after the ninth click i want it to go back to the first column again.

it sounds so simple: x=x+1, if x>9 then x=1: sort by column x

i just don't know vba syntax very well. so without further adu here is the
code i would like to add a counter to...

'range to sort
Worksheets("Sheet1").Range("A2:I100").Sort _
'column to sort by
Worksheets("Sheet1").Range("A1")

thank you very much for any help,

marc

 

[excelent]

cell J1 holds the variable (1 to 9)

Sub Makro2()
[J1] = [J1] + 1: If [J1] > 9 Then [J1] = 1
Range("A1:I100").Sort Key1:=Range(Chr([J1] + 64) & 2), Order1:=xlAscending
'MsgBox ("") & Range(Chr([J1] + 64) & 2).Address
End Sub


"mmadden2" skrev:

 

[Joe]

hi all,

i've come across some nifty sorting code and it works well for me (excel
2003). so far i am able to create a forms button and assign it to this macro.
here's how it currently works...

i have data in 9 columns. row 1 is the headers. when you click the button,
data from one of the columns is sorted. perfect. what i would really like to
do is click that single button over and over and have the macro cycle from
column to column, sorting as it goes.

example... click (sort by A1), click (sort by B1), click (sort by C1) and so
on. after the ninth click i want it to go back to the first column again.

it sounds so simple: x=x+1, if x>9 then x=1: sort by column x

i just don't know vba syntax very well. so without further adu here is the
code i would like to add a counter to...

'range to sort
Worksheets("Sheet1").Range("A2:I100").Sort _
'column to sort by
Worksheets("Sheet1").Range("A1")

thank you very much for any help,

marc

Try this...
this sequencially sorts each column... but this maynot give proper
result!

Private Sub CommandButton1_Click()
Const NCol As Integer = 9
Dim i as Integer

Columns("A:I").Select
For i = 1 To NCol
Selection.Sort Key1:=Cells(1, i), Order1:=xlAscending _
, Header:=xlYes, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2:=xlSortNormal
Next

End Sub

 

[mmadden2]

your code works great - thank you very much!
i have some questions...

1) can you tell me what this piece does? Sort Key1:=Range(Chr([J1] + 64) & 2)
i assume Chr([J1] mean sort by this column? what does Chr() mean? why does
adding 64 make it work? What is & 2?

2) is there any way of using this macro without storing the value visibly in
a cell? i would prefer not to see the numbers 1-9 scrolling in j1 - i've
managed to move them off to a less visible location on another sheet but i
would think the count value could just be 'remembered' instead of written
down.

much appreciated,

marc

cell J1 holds the variable (1 to 9)

Sub Makro2()
[J1] = [J1] + 1: If [J1] > 9 Then [J1] = 1
Range("A1:I100").Sort Key1:=Range(Chr([J1] + 64) & 2), Order1:=xlAscending
'MsgBox ("") & Range(Chr([J1] + 64) & 2).Address
End Sub


"mmadden2" skrev:

hi all,

i've come across some nifty sorting code and it works well for me (excel
2003). so far i am able to create a forms button and assign it to this macro.
here's how it currently works...

i have data in 9 columns. row 1 is the headers. when you click the button,
data from one of the columns is sorted. perfect. what i would really like to
do is click that single button over and over and have the macro cycle from
column to column, sorting as it goes.

example... click (sort by A1), click (sort by B1), click (sort by C1) and so
on. after the ninth click i want it to go back to the first column again.

it sounds so simple: x=x+1, if x>9 then x=1: sort by column x

i just don't know vba syntax very well. so without further adu here is the
code i would like to add a counter to...

'range to sort
Worksheets("Sheet1").Range("A2:I100").Sort _
'column to sort by
Worksheets("Sheet1").Range("A1")

thank you very much for any help,

marc

 

[mmadden2]

hi joe,

thanks for the quick reply. i don't see where a range can be assigned to
what is being sorted. i have information further down the sheet that cannot
get mixed in with things up top. i need to sort a particular range by cycling
thru the columns.

example...

click
sort range a2:j100 based on col a
click
sort range a2:j100 based on col b
click
sort range a2:j100 based on col c
9 times then back to...
sort range a2:j100 based on col a

thank you,

marc

 

[Joe]

hi joe,

thanks for the quick reply. i don't see where a range can be assigned to
what is being sorted. i have information further down the sheet that cannot
get mixed in with things up top. i need to sort a particular range by cycling
thru the columns.

example...

click
sort range a2:j100 based on col a
click
sort range a2:j100 based on col b
click
sort range a2:j100 based on col c
9 times then back to...
sort range a2:j100 based on col a

thank you,

marc









- Show quoted text -

Hi Marc,

u can select a specific range as well..
Replace "Columns("A:I").Select" with "Range("A2:J100").Select"

Regrads
Joe

 

[excelent]

Chr(65) - returns A
Chr(66) - returns B and so on

cell J1 = 1 - and increase by 1 each time u run the code
Chr([J1] + 64) = 65 = A (column A)
Chr([J1] + 64) & 2 = A2 (cell A2)

I dont no any other way than write it to a cell - sorry
but as u no allready u can move it to another location or sheet
ex. Sheet2 cell A1
[Sheet2!A1] insted of [J1]

regards Poul
"mmadden2" skrev:

your code works great - thank you very much!
i have some questions...

1) can you tell me what this piece does? Sort Key1:=Range(Chr([J1] + 64) & 2)
i assume Chr([J1] mean sort by this column? what does Chr() mean? why does
adding 64 make it work? What is & 2?

2) is there any way of using this macro without storing the value visibly in
a cell? i would prefer not to see the numbers 1-9 scrolling in j1 - i've
managed to move them off to a less visible location on another sheet but i
would think the count value could just be 'remembered' instead of written
down.

much appreciated,

marc

cell J1 holds the variable (1 to 9)

Sub Makro2()
[J1] = [J1] + 1: If [J1] > 9 Then [J1] = 1
Range("A1:I100").Sort Key1:=Range(Chr([J1] + 64) & 2), Order1:=xlAscending
'MsgBox ("") & Range(Chr([J1] + 64) & 2).Address
End Sub


"mmadden2" skrev:

hi all,

i've come across some nifty sorting code and it works well for me (excel
2003). so far i am able to create a forms button and assign it to this macro.
here's how it currently works...

i have data in 9 columns. row 1 is the headers. when you click the button,
data from one of the columns is sorted. perfect. what i would really like to
do is click that single button over and over and have the macro cycle from
column to column, sorting as it goes.

example... click (sort by A1), click (sort by B1), click (sort by C1) and so
on. after the ninth click i want it to go back to the first column again.

it sounds so simple: x=x+1, if x>9 then x=1: sort by column x

i just don't know vba syntax very well. so without further adu here is the
code i would like to add a counter to...

'range to sort
Worksheets("Sheet1").Range("A2:I100").Sort _
'column to sort by
Worksheets("Sheet1").Range("A1")

thank you very much for any help,

marc