VBA：自动点评排名靠前、靠后的部门/单位等

前言：之前用python写过一个根据排名自动点评的程序，但是使用的openpyxl等很难打包的库，所以考虑是否可以用vba来写
花了1个小时，终于写出来了，代码很短，但是效果还是不错的。

一、表格内容：


二、点评效果：

三、代码

Sub 代理商情况通报宏(blank As String)
    
'构建字典，key是原始列（需要复制的），value是目标列（需要黏贴的）
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
dict.Add "宽带", "F23:F34"
dict.Add "宽带提速包", "N23:N34"
dict.Add "移动", "V23:V34"
dict.Add "新魔都（不含免费赠卡）", "AC23:AC34"

Sheets("群通报").Select
Dim good As String
Dim bad As String
Dim comment As String

step = 1
For Each k In dict:
    v = dict.Item(k)
    good = ""
    bad = ""
    For Each i In Range(v):
        i_row = i.Row
        If i <= 2 And good = "" Then
            good = Range("A" & i_row)
        ElseIf i <= 2 And good <> "" Then
            good = good & "、" & Range("A" & i_row)
        ElseIf i >= 11 And bad = "" Then
            bad = Range("A" & i_row)
        ElseIf i >= 11 And bad <> "" Then
            bad = bad & "、" & Range("A" & i_row)
        End If
    Next
    If comment = "" Then
        comment = "(" & step & ")" & k & Chr(10) & "排名靠前：" & good & "，" & "排名靠后：" & bad & "；"
        step = step + 1
    Else:
        comment = comment & Chr(10) & "(" & step & ")" & k & Chr(10) & "排名靠前：" & good & "，" & "排名靠后：" & bad & "；"
        step = step + 1
    End If
Next
Range("B39") = comment '把最终的结果输出到单元格

End Sub