用于搜索列并对其进行排序的VBA代码

Question

我正在写一个VBA代码，必须找到工作表上的特定列，并按特定的citeria排序。这是我的代码

Sub SortExchangesEurope()

    Dim intCounter As Integer
    Dim rngExchange As Range
    intCounter = 1

    Do While Worksheets("Sheet1").Cells(2, intCounter) <> ""
        If Worksheets("Sheet1").Cells(2, intCounter).Value = "Exchange" Then
            With Worksheets("Sheet1")
                Set rngExchange = .Range(.Cells(2, intCounter), .Cells(2, intCounter))
               MsgBox intCounter
             End With
        End If

        intCounter = intCounter + 1
    Loop

    Worksheets("Sheet1").Range("$A$2:$AP$778").AutoFilter Field:=intCounter, Criteria1:=Array( _
        "XBEL", "XBUD", "XBSE", "XQMH", "XWAR", _
        "BMEX", "XLIS", "XLIT", "XBUL", "ASEX", _
        "XDUB", "XBRU", "XLUX", "XSTO", "XSWX", _
        "XHEL", "XMOS", "MISX", "XCSE", "XVTX", _
        "IEPA", "XMIL", "XLIS", "BMEX", _
        "XLJU", "ASEX", "XRIS", "XBRA", "xlOn", _
        "XOSL", "XPAR", "XPRA", "XICE", "XIST", _
        "XTAL", "XMIL", "XTRN", "XBRU", "XLDN", _
        "XAMS", "XZAG", "XSWX", "XATH", "XMAD", "XOME", _
        "XMRV", "XADE", "XVTX", "XTAH", "RTSX", "XLTO", _
        "XDMI", "MFOX", "XMAD", "XMAT", "XTLX", "ICEU", _
        "XMON", "XTUR", "XBRD", "XEDX", "XOME", "XLIF"), Operator:=xlFilterValues

End Sub

第一部分查找名称所在的列，第二部分必须只对其中包含给定代码的字段进行排序。这两个部分单独工作很好，但它们不能一起工作。

自动筛选范围类失败-我收到此错误

Answer 1

在使用AutoFilter之前，您必须检查并设置AutoFilterMode

Sub SortExchangesEurope()
    Dim wS As Worksheet
    Dim intCounter As Integer
    Dim rngExchange As Range

    Set wS = Sheets("Sheet1")
    intCounter = 1

    With wS
        Do While .Cells(2, intCounter).Value <> "Exchange" Then
           intCounter = intCounter + 1
        Loop
        Set rngExchange = .Range(.Cells(2, intCounter), .Cells(2, intCounter))

        '.Activate
        '.Range("B2").Activate
        If Not .AutoFilterMode Then .AutoFilterMode = True

        .Range("$A$2:$AP$778").AutoFilter Field:=intCounter, Criteria1:=Array( _
            "XBEL", "XBUD", "XBSE", "XQMH", "XWAR", _
            "BMEX", "XLIS", "XLIT", "XBUL", "ASEX", _
            "XDUB", "XBRU", "XLUX", "XSTO", "XSWX", _
            "XHEL", "XMOS", "MISX", "XCSE", "XVTX", _
            "IEPA", "XMIL", "XLIS", "BMEX", _
            "XLJU", "ASEX", "XRIS", "XBRA", "xlOn", _
            "XOSL", "XPAR", "XPRA", "XICE", "XIST", _
            "XTAL", "XMIL", "XTRN", "XBRU", "XLDN", _
            "XAMS", "XZAG", "XSWX", "XATH", "XMAD", "XOME", _
            "XMRV", "XADE", "XVTX", "XTAH", "RTSX", "XLTO", _
            "XDMI", "MFOX", "XMAD", "XMAT", "XTLX", "ICEU", _
            "XMON", "XTUR", "XBRD", "XEDX", "XOME", "XLIF"), Operator:=xlFilterValues

    End With 'wS

End Sub