Home>

Always I am indebted.

This time, I asked a question because the Excel macro causes an error.

【error contents】
Macros that work normally on my PC stop with an error on the way on other PCs.
It stops with an error at the part of the instruction that sorts in numerical order.

-Runtime error is "438"
・ The error is the code below
ActiveWorkbook.Worksheets ("Payment Request List"). Sort.SortFields.Add2 Key: = Range (_ "A2: A1048576"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _ xlSortNormal

・ Both Excel versions are "2016"
-Both macro security is "execute all macros"
・ PC is "Windows 10"
・ Introduce my Excel file to another PC as it is

MsgBox "Before Sorting"
             'Sort in ascending number order
             Windows ("Payment application list.xlsm"). Activate
             Columns ("A: N"). Select
    ActiveWorkbook.Worksheets ("Payment Request List"). Sort.SortFields.Clear
   ** ActiveWorkbook.Worksheets ("Payment Request List"). Sort.SortFields.Add2 Key: = Range (_
        "A2: A1048576"), SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = _
        xlSortNormal **
    With ActiveWorkbook.Worksheets ("Payment Request List"). Sort
        .SetRange Range ("A1: N1048237")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

If there is any other necessary information, I will describe it.
I look forward to working with you.

  • Answer # 1

    Why not try Add instead of Add2?
    It seems that Add2 cannot be used depending on how Windows Update is applied.

  • Answer # 2

    The Add2 method seems to have been enhanced in 2016, and there may be environments where it cannot be used in 2016.
    How about replacing Add2 with Add?