时间戳

首页 > 解决方案 > 值不是按字母顺序排序的

excel - 值不是按字母顺序排序的

问题描述

当我在调用 filter_deals 后中断代码时，我查看 Sheet8（我要粘贴到的工作表）并看到我想要的输出以正确的顺序排序。但是，当我不中断并让代码从头到尾一直运行时，这些值不会按我希望的那样排序。我希望粘贴的每个数据范围都按字母顺序排序。

我尝试取消过滤要从中复制范围的工作表

Sub run_level1()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim num_classificaitons As Integer
Dim num_sub_classifications As Integer
Dim sub_classification1 As String
Dim sub_classification2 As String
Dim sub_classification3 As String
Dim irr As Variant
Dim moic As Variant
Dim dpi As Variant
Dim counter As Integer
Dim num_sub_classifications1 As Integer
Dim num_sub_classifications2 As Integer
Dim num_sub_classifications3 As Integer

On Error Resume Next
    Sheet2.ShowAllData


'Calculate Dataset IRR and paste it in output tab

'Call calculate_dataset_irr


'Filters Holdings and Pastes Respective Inputs into Input Lists tab

Call filter_deals


'Run Level 1

counter = 0

Sheet10.Range("B3:E10000").Clear
Sheet3.Range("C1:C8").Clear
Sheet3.Range("E1:E5").NumberFormat = "@"

num_classifications = Sheet8.Cells(1, 10)

For i = 1 To num_classifications
    num_sub_classifications1 = Sheet8.Cells(1, 1 + i)
    For k = 1 To num_sub_classifications1
        sub_classification1 = Sheet8.Cells(2 + k, i + 1).Value2
        sub_classification2 = ""
        sub_classification3 = ""

        Sheet3.Range("C1:C6").NumberFormat = "@"
        Sheet3.Cells(0 + i, 3) = sub_classification1
        Sheet2.Calculate
        Sheet3.Calculate

        irr = Sheet3.Cells(11, 3)

        Sheet10.Cells(3 + counter, 2) = sub_classification1
        Sheet10.Cells(3 + counter, 3) = sub_classification2
        Sheet10.Cells(3 + counter, 4) = sub_classification3
        Sheet10.Cells(3 + counter, 5) = irr

        counter = counter + 1
        Sheet3.Range("C1:C6").Clear
        'Application.StatusBar = "Level 1 Progress: " & counter & " of 194"
    Next k
Next i

End Sub

Sub filter_deals()

Dim filterby1 As String
Dim filterby2 As String
Dim filterto1 As String
Dim filterto2 As String
Dim column1 As Integer
Dim column As Integer
Dim end_range As Integer
Dim i As Integer
Dim ev_ids As Range
Dim all As Range

Sheet8.Range("B3:H1000").Clear
filterby1 = Sheet3.Cells(1, 5)
filterby2 = Sheet3.Cells(3, 5)

filterto1 = Sheet3.Cells(2, 5)
filterto2 = Sheet3.Cells(4, 5)

On Error Resume Next
    Sheet1.ShowAllData

Sheet1.Range("$A$1:$BH$1000").AutoFilter Field:=58, Criteria1:="<>"
Sheet1.Range("BG1:BG1000").AutoFilter Field:=23, Criteria1:="<>"

If filterby2 = "" And filterby1 <> "" Then
    column1 = WorksheetFunction.Match(filterby1, Sheet1.Range("1:1"), 0)
    Sheet1.Range("$A$1:$BG$1000").AutoFilter Field:=column1, Criteria1:=filterto1
End If

If filterby2 <> "" And filterby1 <> "" Then
    column1 = WorksheetFunction.Match(filterby1, Sheet1.Range("1:1"), 0)
    Sheet1.Range("$A$1:$BG$1000").AutoFilter Field:=column1, Criteria1:=filterto1
    column2 = WorksheetFunction.Match(filterby2, Sheet1.Range("1:1"), 0)
    Sheet1.Range("$A$1:$BG$1000").AutoFilter Field:=column2, Criteria1:=filterto2

End If


'Paste Unique Modified Strategies
With Sheet1
    .Range("BD2", .Range("BD2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("B3")
End With

With Sheet8
    .Range("B3", .Range("B3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("B3", .Range("B3").End(xlDown)).Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Investment Types

With Sheet1
    .Range("BE2", .Range("BE2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("C3")
End With

With Sheet8
    .Range("C3", .Range("C3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("C3", .Range("C3").End(xlDown)).Sort Key1:=Range("C3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Vintages

With Sheet1
    .Range("BG2", .Range("BG2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("D3")
End With

With Sheet8
    .Range("D3", .Range("D3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("D3", .Range("D3").End(xlDown)).Sort Key1:=Range("D3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Underwriting Analysts

With Sheet1
    .Range("BF2", .Range("BF2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("E3")
End With

With Sheet8
    .Range("E3", .Range("E3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("E3", .Range("E3").End(xlDown)).Sort Key1:=Range("E3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Investment Status

With Sheet1
    .Range("BC2", .Range("BC2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("F3")
End With

With Sheet8
    .Range("F3", .Range("F3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("F3", .Range("F3").End(xlDown)).Sort Key1:=Range("F3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Asset Class

With Sheet1
    .Range("BB2", .Range("BB2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("G3")
End With

With Sheet8
    .Range("G3", .Range("G3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("G3", .Range("G3").End(xlDown)).Sort Key1:=Range("G3"), Order1:=xlAscending, Header:=xlNo
End With



'Paste Unique Deal Names

With Sheet1
    .Range("AZ2", .Range("AZ2").End(xlDown)).SpecialCells(xlCellTypeVisible).copy Destination:=Sheet8.Range("H3")
End With

With Sheet8
    .Range("H3", .Range("H3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("H3", .Range("H3").End(xlDown)).Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlNo
End With

On Error Resume Next
    Sheet1.ShowAllData

Sheet8.Calculate


End Sub

我希望粘贴的每一列都按升序排列

标签： excelvbasorting

解决方案

我会看看这个和其他有同样问题的块：

With Sheet8
    .Range("G3", .Range("G3").End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    .Range("G3", .Range("G3").End(xlDown)).Sort Key1:=Range("G3"), Order1:=xlAscending, Header:=xlNo
End With

查看该单元格如何引用

Key1:=Range("G3")

不符合工作表参考或领先时期？

如果未选择 Sheet8，则默认情况下将引用ActiveSheetand 失败（由于 ，静默On Error resume Next），因为排序键需要与排序范围位于同一工作表上。

如果 Sheet8 恰好处于活动状态，它不会失败（例如，如果您暂停代码并查看该工作表）

推荐阅读