☰

根据颜色对值进行排序，然后使用 VBA 代码对值进行排序

tag-icon • tag-icon microsoft-excel vba macros
根据颜色对值进行排序，然后使用 VBA 代码对值进行排序

我尝试先根据颜色对值进行排序，然后根据值对值进行排序

宏运行得很好，但只能在一张纸上，所以我必须为每张纸创建一个新模块，因为我有大约 400 张纸，

这是简单的工作宏：

    Sub A_Sort()
'
' A_Sort Macro
'

'
    Range("B4:J43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Jan_3").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Jan_3").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select

最好的方法是针对活动工作表运行宏，而不是针对命名工作表运行宏

这是我的尝试，但它不起作用，我不知道如何重写代码以在活动表上工作

Sub A_Sort()
'
' A_Sort Macro
'

'
    Dim WS As Worksheet

    Set WS = ActiveSheet

    With WS.Sort
    Range("B4:J43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
        208, 142)
    WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
        176, 132)
   WS.Range("G3:G43").Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
        137, 219)
    WS.Range("G3:G43")).Sort.SortFields.Add(Range("B4:B43"), _
        xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
        194, 230)
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("G4:G43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B3:J43")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4:B43").Select
    WS.Range("G3:G43").Sort.SortFields.Clear
   WS.Range("G3:G43").Sort.SortFields.Add2 Key:=Range("B4:B43") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With WS.Range("G3:G43").Sort
        .SetRange Range("B4:B43")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("C4").Select
End Sub

非常感谢

答案1

试试这个，如果有效请告诉我。你似乎误解了“With”的作用。当你用“With WS”封装所有内容时，你不必再使用“WS。”前缀。你只需要“。”

Sub A_Sort()
    Dim WS As Worksheet
    Set WS = ActiveSheet
    
    With WS
        .Range("B4:J43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add(.Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(169, _
            208, 142)
        .Sort.SortFields.Add(.Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(244, _
            176, 132)
        .Sort.SortFields.Add(.Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(184, _
            137, 219)
        .Sort.SortFields.Add(.Range("B4:B43"), _
            xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(155, _
            194, 230)
        .Sort.SortFields.Add2 Key:=.Range("G4:G43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange WS.Range("B3:J43")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("B4:B43").Select
        .Sort.SortFields.Clear
        .Sort.SortFields.Add2 Key:=.Range("B4:B43") _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange WS.Range("B4:B43")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Range("C4").Select
    End With
End Sub

相关内容