Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting by Code (Excel 2K sr1a)

    I have a workbook with several sheets, two of which are similar. I have automated a Sort with the following function, called from the Before_save event of the workbook and it works. However, I set it up as a temporary measure until I worked out how to incorporate the range names into the code and this I can't get to work. The range is dynamic so grows with input and could currently end on F250 for example. I have this worked properly it just doesn't seem to transfer to code. I would have thought I could replace "A6:F1000" with a string expression for the Name but I can't.
    Any advice out there?

    Function Reorder(ByVal strSheet As String)
    Worksheets(strSheet).Range("a6:f1000").Sort _
    key1:=Worksheets(strRange).Range("A6")
    End Function

    Thanks
    Peter

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Are you sure this works? I would be surprised if it does since you should not be able to sort from a Function. However, I just tried it and it does seem to work. Does anyone else out there know how this gets around the restriction of a function modifying a sheet directly?

    There also does not seem to be anything to give strRange a value, unless it is a Global variable.

    Are you talking about using a Named Range for the sort range? If so, I created a range on Sheet1 named SortRange, and the following code worked fine for me in a Sub procedure:

    <pre>Dim strSheet As String, strRange As String
    strSheet = "Sheet1"
    strRange = "Sheet1"
    Worksheets(strSheet).Range("SortRange").Sort _
    key1:=Worksheets(strRange).Range("A6")
    </pre>



    I also tried this, and it seemed to work just fine:

    <pre>Function Reorder(ByVal strSheet As String, ByVal strRange As String) As Integer
    Worksheets(strSheet).Range("SortRange").Sort _
    key1:=Worksheets(strRange).Range("A6")
    End Function


    Public Sub Test2()
    A = Reorder("Sheet1", "Sheet1")
    End Sub
    </pre>


    Are you doing something different?
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Legare
    Yes it definitely works on the basis it is currently entered. I've included the sub that calls the function this time and I can assure you it works exactly as it's supposed to.
    All I would like to do is replace the Range ref A6:F1000 with the Names ("OrderBus" and "OrderPers") so I don't need to go in and alter the code if the range grows too big. It must be possible but I can't make it work.

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Reorder "Business"
    Reorder "Personal"
    lngResult = MsgBox(UCase("is it ok to save now?"), 292, "Save")
    If lngResult = 7 Then Cancel = True
    End Sub

    Function Reorder(ByVal strRange As String)
    Worksheets(strRange).Range("a6:f1000").Sort _
    key1:=Worksheets(strRange).Range("A6")
    End Function

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Legare
    I'd replied before reading your post properly. There was a misprint in the first post. I'm looking to include a string to cover the actual range for the 2 sheets as well as the name of the 2 sheets themselves (Business and Personal = sheets - OrderBus and OrderPers = the sort range on each sheet.) I just keep getting an error when I replace "A6:F1000" with a string to replace the range Names.
    Peter

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Legare

    It works just fine with the Named Ranges as variables. The reason I was getting an error was that I had a typing error in the definition of one of the dynamic Named Ranges.

    Check the simple things first before you play around with the complicated stuff and go asking for other people's help, eh?

    Thanks for your help, anyway.

    Peter

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    After no testing <bg> I would say that the no-changes restriction only is valid for functions (udf's) called from a worksheet cell.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Just a side remark.

    I see you have only specified one sort key and have omitted the others. I'm not sure about the Sort method, but with the Find method Excel uses the options that are not set as they were used druing the last find done. You might get into nasty surprises that way. I would explicitly set all options the way you need them in your code to avoid possible erraric behaviour.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    After testing, it appears that you are correct! I had always thought that the restriction was absolute.
    Legare Coleman

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Jan Karel
    You are right. Some of the options are carried forward but as ny method uses the defaults and I can't think of a time I have changed this I think I'll be OK. The worst that should happen is a descending sort instead of ascending.
    Thanks.
    Peter

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sorting by Code (Excel 2K sr1a)

    Jan Karel,
    You are correct - you can do anything with a function that you can do with a sub as long as you don't call it from a worksheet. Using a function rather than a sub simply allows you to return a value to the calling procedure - e.g. success or failure. Of course, if you want to run it from the macros dialog or a toolbar, you need a calling sub too.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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