vba中对活动数据实现排行榜

Option Explicit

Sub 跨服顶寻消耗活动()
Dim b As Workbook, i%
    Set b = Workbooks.Open("C:\Users\admin\Desktop\跨服顶寻消耗活动.xls")
    b.Activate
    Windows("跨服顶寻消耗活动.xls").Activate
'把B列剪切到E列前面
    Worksheets(1).Columns("C:C").Cut
    Worksheets(1).Columns("B:B").Insert Shift:=xlToRight

'定义一个range().sort，然后定义Key1，order1
'range()定义整个数据范围，Key1用于定义哪一列，order1定义升序Ascend或者降序Descend
    Worksheets(1).Range("A:E").Sort _
    Key1:=Worksheets(1).Cells(1, 5), order1:=xlDescending
    i = 1
    Do While Cells(i, 5) <> ""
        If i > 51 Then
            Rows(i).Delete
            i = i - 1
        End If
        i = i + 1
    Loop
'在各行处插入空白行
    Rows("12:12").Select
    For i = 1 To 7
    Selection.Insert Shift:=xlDown
    Next i
    
    
    Rows("5:5").Select
    For i = 1 To 5
    Selection.Insert Shift:=xlDown
    Next i
    
    Rows("4:4").Select
    For i = 1 To 3
    Selection.Insert Shift:=xlDown
    Next i
    
    Rows("3:3").Select
    For i = 1 To 2
    Selection.Insert Shift:=xlDown
    Next i
'第一名奖励
Range("F2").Value = "第1名"
    If Range("E2").Value > 2000 Then
        Range("G2").Value = "gold": Range("H2").Value = "3000000"
        Range("J2").Value = "": Range("K2").Value = ""
        Range("M2").Value = "": Range("N2").Value = ""
        Range("P2").Value = "32002": Range("Q2").Value = "3888"
        Range("L2").Value = "获得隐藏大圣": Range("L2").Interior.Color = vbYellow
    End If
    If Range("E2").Value <= 2000 Then
        Range("G2").Value = "gold": Range("H2").Value = "1500000"
        Range("J2").Value = "20005": Range("K2").Value = "40"
        Range("M2").Value = "10005": Range("N2").Value = "40"
        Range("P2").Value = "32002": Range("Q2").Value = "3000"
    End If
'第2名奖励
Range("F5").Value = "第2名"
    Range("G5").Value = "gold": Range("H5").Value = "1200000"
    Range("J5").Value = "20005": Range("K5").Value = "20"
    Range("M5").Value = "10005": Range("N5").Value = "30"
    Range("P5").Value = "32002": Range("Q5").Value = "2500"
'第3名奖励
Range("F9").Value = "第3名"
    Range("G9").Value = "gold": Range("H9").Value = "800000"
    Range("J9").Value = "20005": Range("K9").Value = "10"
    Range("M9").Value = "10005": Range("N9").Value = "20"
    Range("P9").Value = "32002": Range("Q9").Value = "2000"
'第4-10名奖励
Range("F15").Value = "第4-10名"
    Range("G15").Value = "gold": Range("H15").Value = "500000"
    Range("J15").Value = "20005": Range("K15").Value = "5"
    Range("M15").Value = "10005": Range("N15").Value = "10"
    Range("P15").Value = "32002": Range("Q15").Value = "1000"
'第11-50名奖励
Range("F29").Value = "第11-50名"
    Range("G29").Value = "gold": Range("H29").Value = "200000"
    Range("J29").Value = "20005": Range("K29").Value = "3"
    Range("M29").Value = "10005": Range("N29").Value = "5"
    Range("P29").Value = "32002": Range("Q29").Value = "500"
'自动调整列宽
    Columns("A:Q").EntireColumn.AutoFit
End Sub