variable sort macro?

[baseball]

I'm sure this question has been asked and answered before, but I haven't come up with the right search criteria.

I have a database in Excel97:

C/R A B C etc.
1 name11 data11 data12
2 name21 data21 data23
3 name31 data31 data33
etc.

The columns are fixrd, but the rows vary.

I frequently need to sort by column A or B, or AJ. I'd like to use a macro with a variable so I could tell it which column to sort on, either by entering the data (actual range or dynamic range name) or by attaching it to a control.

Can anyone get me started in the right direction, remembering that I don't know VBA?

Thanks,
Cliff

 

[kkknie]

Open the Forms toolbar (View | Toolbars | Forms)

Select a command button and put it on your worksheet.

When the assign macro dialog comes up, choose New.

Put this code between the Sub and End Sub statements
(the sub should probably be Sub Button1_Click).


dim strCol as String
strCol = InputBox("Enter Column to Sort")

strCol = strCol & "1"

Range("A1:AJ100").Select
Selection.Sort Key1:=Range(strCol), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom



Now whenever you push the button it will bring up a dialog
box asking for a column letter and do a sort on it.

Notes:

The Range statement should include you whole data range.
The "1" in strCol = strCol & "1" should be changed to
be the row number for the first row you want sorted.
The Header:=xlYes assumes that the first row of your
range should not be sorted (because it is a header). Change
this to xlNO if the header is not included in your range.
You will get erros if you do not enter valid row letter(s).

Hope this helps.

K

 

[baseball]

On 2002-05-03 09:11, kkknie wrote:

Open the Forms toolbar (View | Toolbars | Forms)

Select a command button and put it on your worksheet.

When the assign macro dialog comes up, choose New.

Put this code between the Sub and End Sub statements
[etc.]

Works like a charm. This will save me a lot of work. Thank you very much for helping me.

Cliff