+ Reply to Thread
Results 1 to 8 of 8

Passing a range as an argument for a subprocedure

  1. #1
    Registered User
    Join Date
    06-23-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2010/2007
    Posts
    34

    Passing a range as an argument for a subprocedure

    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!
    Last edited by secondrate; 06-24-2010 at 09:50 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Passing a range as an argument for a subprocedure

    Welcome to the forum.

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

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    06-23-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2010/2007
    Posts
    34

    Re: Passing a range as an argument for a subprocedure

    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]

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Passing a range as an argument for a subprocedure

    Maybe like this (untested, and written in Excel 2003, so it doesn't compile)
    Please Login or Register  to view this content.

  5. #5
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Passing a range as an argument for a subprocedure

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    06-23-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2010/2007
    Posts
    34

    Re: Passing a range as an argument for a subprocedure

    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

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Passing a range as an argument for a subprocedure

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-23-2010
    Location
    Singapore
    MS-Off Ver
    Excel 2010/2007
    Posts
    34

    Re: Passing a range as an argument for a subprocedure

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1