Passing a range as an argument for a subprocedure

[secondrate]

Hey guys, so i'm making a very simple project which gives the user some data fields and upon choosing 1, the macro will sort the data either in an ascending or descending manner. (note: not all of the values in the data fields are numeric, some of them require a custom list for ordering). Anyway, i'm stuck with the code because it seems that it causes an error when i pass of a range as an argument for a subprocedure. by the way, i named the ranges in excel, for example, column E is named as "Offer"

Code:

1 Public Sub SortValueAscending()
2
3 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
4 ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("E2:E274"), _
5 SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
6 With ActiveWorkbook.Worksheets("Data").Sort
7 .SetRange Range("A1").CurrentRegion
8 .Header = xlGuess
9 .MatchCase = False
10 .Orientation = xlTopToBottom
11 .SortMethod = xlPinYin
12 .Apply
13 End With
14 End Sub
15
16 Private Sub OKButton_Click()
17
18 If UserForm1.OptionButton1.Value = True And UserForm1.OptionButton7.Value = True 19 Then Call SortValueAscending

As you can see, I want to pass the "Offer" range as input in line 1 and on line 4, change the range you see labeled "E1:E174" to "Offer" and lastly, on line 19, i want to have it something like this: SortValueAscending(...) where the "Offer" column is the argument. It seems that there's an error maybe because of my syntax in passing the range as argument. Anyone can help? Thanks!

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[secondrate]

Hey guys, so i'm making a very simple project which gives the user some data fields and upon choosing 1, the macro will sort the data either in an ascending or descending manner. (note: not all of the values in the data fields are numeric, some of them require a custom list for ordering). Anyway, i'm stuck with the code because it seems that it causes an error when i pass of a range as an argument for a subprocedure. by the way, i named the ranges in excel, for example, column E is named as "Offer"

Code:

Please Login or Register  to view this content.

As you can see, I want to pass the "Offer" range as input in line 1 and on line 4, change the range you see labeled "E1:E174" to "Offer" and lastly, on line 19, i want to have it something like this: SortValueAscending(...) where the "Offer" column is the argument. It seems that there's an error maybe because of my syntax in passing the range as argument. Anyone can help? Thanks![/QUOTE]

[shg]

Maybe like this (untested, and written in Excel 2003, so it doesn't compile)

Please Login or Register  to view this content.

[snb]

Please Login or Register  to view this content.

[secondrate]

Oh just a reminder, I named the range E1:E174 as "Offer". as much as possible, i'd like to have it that way than using E1:E174 because if this macro will be exported to other worksheets, "offer" wouldn't always be in that column

[shg]

Please Login or Register  to view this content.

[secondrate]

Ok...i seem to have figured it out.THANKS!