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:

 Public Sub SortValueAscending()

   ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("E2:E274"), _
       SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Data").Sort
       .SetRange Range("A1").CurrentRegion
        .Header = xlGuess
       .MatchCase = False
        .Orientation = xlTopToBottom
       .SortMethod = xlPinYin
        .Apply
   End With
 End Sub

 Private Sub OKButton_Click()

 If UserForm1.OptionButton1.Value = True And UserForm1.OptionButton7.Value = True  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![/QUOTE]

[shg]

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

Public Sub SortValueAscending(rSort As Range, rKey As Range)
    With rSort.Parent.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rKey, _
                        SortOn:=xlSortOnValues, _
                        Order:=xlAscending, _
                        DataOption:=xlSortNormal
        .SetRange Range("A1").CurrentRegion
        .Header = xlGuess   ' there's no need to guess; change to xlYes or xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub


Private Sub OKButton_Click()
    Dim someSortRange As Range
    Dim someKeyCell As Range
    
    Set someSortRange = ActiveSheet.UsedRange   ' or something else
    Set someKeyCell = Range("E1")               ' or something else

    If UserForm1.OptionButton1.Value And _
       UserForm1.OptionButton7.Value Then SortValueAscending someSortRange, someKeyCell
End Sub

[snb]

Public Sub SortValueAscending()
   With ThisWorkbook.Worksheets("Data")
       .cells(1).currentregion.Sort .[E2]
   End With
End Sub

Private Sub OKButton_Click()
   If OptionButton1 *OptionButton7= 1  Then SortValueAscending
End sub

[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]

Set someKeyCell = Intersect(Range("Offer"), ActiveSheet.UsedRange)

[secondrate]

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