locked
Macro with named range working in Excel365 and not in Excel2010 (error 438) RRS feed

  • Question

  • I have a macro that is working fine in my Excel version (365), but not on another PC with Excel2010. It's breaks on the bold part. Both running Windows10.

    Sub SorterenDeelnemersOpAchternaam()
    '
    ' SorterenDeelnemersOpAchternaam Macro
    ' Sorteren van de de vaste deelnemers op achternaam.
    '

    '
        Sheets("Deelnemers en kosten").Select
        ActiveSheet.Unprotect
        Application.Goto Reference:="VasteDeelnemers"
        ActiveWorkbook.Worksheets("Deelnemers en kosten").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Deelnemers en kosten").Sort.SortFields.Add2 Key:= _
            ActiveWorkbook.Sheets("Deelnemers en kosten").Range("Achternamen"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Deelnemers en kosten").Sort
            .SetRange ActiveWorkbook.Sheets("Deelnemers en kosten").Range("VasteDeelnemers")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Application.Goto Reference:="CellEersteDeelnemer"
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFiltering:=True
       End Sub

    Friday, November 16, 2018 4:36 PM

All replies

  • There is probably an upgrade in the sorting technique used by Office 365 and it is not backwards compatible with xl2010.

    Your code example appears to be simple recorded code so try recording the macro on the computer with xl2010. Mostly I have found that the code will then work on later versions of Excel.


    Regards, OssieMac

    Sunday, November 18, 2018 3:12 AM
  • I tried already recording the macro on the older PC. The recording takes an absolute reference to cells, not a named range. The code itself is identical for both recordings. I just edited the absolute range into the named one.

    I checked the Visual C++ versions and Visual Studio tools versions already. Are the same.

    Sunday, November 18, 2018 2:00 PM
  • My testing of the code does not record identical in both xl2010 and xl2016.

    In xl2016 the recorded code returns the following for the sort key

        .Sort.SortFields.Add2 Key:=

    In xl2010 there is no 2 appended to Add and is as follows:

    .Sort.SortFields.Add Key:=

    If you drop the 2 off Add then it appears to work satisfactorily in both versions irrespective of whether using the absolute ranges or named ranges.

    PS. Some additional info that I should have included with the above.

    Using the code as recorded in xl2016 will not work in xl2010 even with the absolute ranges. It requires "Add2" to be edited to "Add".


    Regards, OssieMac


    • Edited by OssieMac Tuesday, November 20, 2018 1:03 AM
    Sunday, November 18, 2018 10:58 PM
  • Hi TjerKHB,

    Did OssieMac's answer resolved your problem?

    You can mark it as answer if it helped and please help us close the thread.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, November 20, 2018 8:01 AM