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

"excelent" wrote:

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 x9 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