|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- ption Explicit
- Const Status$ = "身份"
- Const Title$ = "职称"
- Const PHD$ = "博"
- Const MA$ = "硕"
- Const BA$ = "本"
- Const Coll$ = "专"
- Const PaysCales$ = "薪级"
- Sub Main()
- Dim Ar, I&, Dic, Keys, Items, J&
- Ar = Sheets("员工统计表").UsedRange
- Set Dic = ParseData
- For I = 2 To UBound(Ar)
- If Ar(I, 11) = "干部" Then
- If IsEmpty(Dic(Ar(I, 11))(Ar(I, 16))(Ar(I, 12))) Then
-
- Else
- Keys = Dic(Ar(I, 11))(Ar(I, 16))(Ar(I, 12)).Keys
- Items = Dic(Ar(I, 11))(Ar(I, 16))(Ar(I, 12)).Items
- End If
-
- For J = 0 To UBound(Keys)
- If Ar(I, 14) >= Keys(J) Then
- Cells(I, "t") = Items(J)
- Exit For
- End If
- Next J
- End If
- Next I
- On Error GoTo 0
- End Sub
- Public Function ParseData()
- Dim Dcol, Dic, Dtemp
- Dim Ar, I&
- Ar = Sheets(PaysCales).Range("a1").CurrentRegion
- '=====================列字段===========================
- Set Dcol = CreateObject("Scripting.Dictionary")
- For I = 1 To UBound(Ar, 2)
- Dcol(Ar(1, I)) = I
- Next I
- '=======================================================
- Set Dic = CreateObject("Scripting.Dictionary")
- For I = 2 To UBound(Ar)
- '依据(身份)创建字典
- If IsEmpty(Dic(Ar(I, Dcol(Status)))) Then
- Set Dic(Ar(I, Dcol(Status))) = CreateObject("Scripting.Dictionary")
- End If
- Set Dtemp = Dic(Ar(I, Dcol(Status)))
-
- '依据(身份)(职称)创建字典
- If IsEmpty(Dtemp(Ar(I, Dcol(Title)))) Then
- Set Dtemp(Ar(I, Dcol(Title))) = CreateObject("Scripting.Dictionary")
- End If
- Set Dtemp = Dtemp(Ar(I, Dcol(Title)))
- '依据(身份)(职称)(P|M|B|C)创建字典
-
- If IsEmpty(Dtemp(PHD)) Then Set Dtemp(PHD) = CreateObject("Scripting.Dictionary")
- If IsEmpty(Dtemp(MA)) Then Set Dtemp(MA) = CreateObject("Scripting.Dictionary")
- If IsEmpty(Dtemp(BA)) Then Set Dtemp(BA) = CreateObject("Scripting.Dictionary")
- If IsEmpty(Dtemp(Coll)) Then Set Dtemp(Coll) = CreateObject("Scripting.Dictionary")
-
- Dtemp(PHD)(Ar(I, Dcol(PHD))) = Ar(I, 1)
- Dtemp(MA)(Ar(I, Dcol(MA))) = Ar(I, 1)
- Dtemp(BA)(Ar(I, Dcol(BA))) = Ar(I, 1)
- Dtemp(Coll)(Ar(I, Dcol(Coll))) = Ar(I, 1)
-
- Next I
- Set ParseData = Dic
- Set Dic = Nothing
- Set Dtemp = Nothing
- Set Dcol = Nothing
- End Function
复制代码 |
|