Excel VBA匹配函数忽略更新的排序

Question

好的，我有一个通常按稀有度排序的项目列表，但对于一个特定的操作，我需要对它进行排序，以便match函数对它起作用，所以我将其设置为针对match函数对其进行排序，然后重新排序。然而，match函数会拉出它在排序之前所在的行。有没有人知道如何解决这个问题？下面的代码(我包含了大量的排序代码)：

'Sort for Match Function
Sheets("Item List").Select
    Range("L2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("L2:U2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Add Key:=Range( _
        "L2:L300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Item List").Sort
        .SetRange Range("L1:U300")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Dim x As Integer
x = Application.Match(BuyItem.Value, Range("TraderItems"))
MsgBox (x)


'Sort back to Rarity
Sheets("Item List").Select
    Range("L2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("L2:U2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Add Key:=Range( _
        "R2:R300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Item List").Sort
        .SetRange Range("L1:U300")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Answer 1

我不熟悉Match函数。所以我不能让那部分工作。我被你原来的潜水艇里所有的“选择”代码搞糊涂了。因此，我简单地将范围复制到数组数组中，对范围进行排序，跳过匹配命令，因为我不知道您正在使用的值，然后将原始数字复制回范围中。

我希望这能帮到你

Sub tester()

Dim Arr() As Variant, x As Integer, arr2Rng As Range
ActiveWorkbook.Worksheets("Item List").Activate

    Arr = Range("L1:U300")
    ActiveWorkbook.Worksheets("Item List").Range("L1:U300").Select

    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Item List").Sort.SortFields.Add Key:=Range( _
        "L2:L300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Item List").Sort
        .SetRange Range("L1:U300")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    x = Application.WorksheetFunction.Match(BuyItem.Value, Range("TraderItems"))
    MsgBox (x)


    Range("L1").Select
    Set arr2Rng = Range("L1")
    arr2Rng.Resize(UBound(Arr, 1), UBound(Arr, 2)).Value = Arr


    End Sub