|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub 按钮7_Click()
- Application.ScreenUpdating = False
- arr = ActiveSheet.UsedRange
- Set d1 = CreateObject("scripting.dictionary")
- Set d2 = CreateObject("scripting.dictionary")
- Set d3 = CreateObject("scripting.dictionary")
- Dim brr() As Integer
- For j = 2 To UBound(arr)
- d3(arr(j, 1)) = ""
- If arr(j, 3) = "V" Then
- If d1.exists(arr(j, 1)) Then
- d1(arr(j, 1)) = d1(arr(j, 1)) & "," & Day(arr(j, 2))
- Else
- d1(arr(j, 1)) = Day(arr(j, 2))
- End If
- End If
-
- If arr(j, 4) = "V" Then
- If d2.exists(arr(j, 1)) Then
- d2(arr(j, 1)) = d2(arr(j, 1)) & "," & Day(arr(j, 2))
- Else
- d2(arr(j, 1)) = Day(arr(j, 2))
- End If
- End If
- Next j
- [g2].Resize(d3.Count) = WorksheetFunction.Transpose(d3.keys)
- r = Cells(Rows.Count, "g").End(3).Row
- arr = Range("g1:h" & r)
- For j = 2 To r
- If d1.exists(arr(j, 1)) Then
- If InStr(d1(arr(j, 1)), ",") = 0 Then
- str1 = "中/" & d1(arr(j, 1))
- Else
- crr = Split(d1(arr(j, 1)), ",")
- ReDim brr(0 To UBound(crr))
- For m = 0 To UBound(crr)
- brr(m) = Val(crr(m)) * 1
- Next m
- str1 = "中/"
- str2 = ""
- For k = 0 To UBound(brr)
- str2 = str2 & "," & WorksheetFunction.Small(brr, k + 1)
- Next k
- str1 = str1 & Right(str2, Len(str2) - 1)
- End If
-
- If d2.exists(arr(j, 1)) Then
- If InStr(d2(arr(j, 1)), ",") > 0 Then
- str11 = "■夜/" & d2(arr(j, 1))
- Else
- crr = Split(d1(arr(j, 1)), ",")
- ReDim brr(0 To UBound(crr))
- For m = 0 To UBound(crr)
- brr(m) = Val(crr(m)) * 1
- Next m
- str11 = "■夜/"
- str21 = ""
- For k = 0 To UBound(brr)
- str21 = str21 & "," & WorksheetFunction.Small(brr, k + 1)
- Next k
- str11 = str11 & Right(str21, Len(str21) - 1)
-
- End If
- str1 = str1 & str11
- End If
-
- Else
- If d2.exists(arr(j, 1)) Then
- If InStr(d2(arr(j, 1)), ",") > 0 Then
- str1 = "■夜/" & d2(arr(j, 1))
- Else
- crr = Split(d1(arr(j, 1)), ",")
- ReDim brr(0 To UBound(crr))
- For m = 0 To UBound(crr)
- brr(m) = Val(crr(m)) * 1
- Next m
- str1 = "夜/"
- str2 = ""
- For k = 0 To UBound(brr)
- str2 = str2 & "," & WorksheetFunction.Small(brr, k + 1)
- Next k
- str1 = str1 & Right(str2, Len(str2) - 1)
- End If
- End If
- End If
- arr(j, 2) = str1
- Next j
- Range("g1:h" & r) = arr
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|