|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Private Sub CommandButton1_Click()
- Dim Arr, i&, aa, x$, y$, Myr&, Brr, Arr1
- Dim d, k, t, tt
- Set d = CreateObject("Scripting.Dictionary")
- Sheet1.Activate
- Myr = Cells(Rows.Count, 1).End(xlUp).Row
- Range("k7:bt" & Myr).ClearContents
- x = 年份.Text & "|" & 月份.Text
- Arr = Sheet2.UsedRange
- For i = 5 To UBound(Arr)
- If Arr(i, 1) <> "" Then
- If InStr(Arr(i, 1), "、") Then
- aa = Split(Arr(i, 1), "、")
- x = aa(1) & "|" & aa(2): y = Arr(i, 2)
- If d.exists(x) = False Then Set d(x) = CreateObject("Scripting.Dictionary")
- d(x)(y) = d(x)(y) & i & ","
- End If
- End If
- Next
- k = d.keys: t = d.items
- Arr1 = Range("a7:b" & Myr)
- Brr = Range("k7:bt" & Myr)
- If d.exists(x) Then
- For i = 1 To UBound(Arr1) Step 2
- y = Arr1(i, 2)
- If d(x).exists(y) Then
- tt = d(x)(y)
- tt = Left(tt, Len(tt) - 1)
- If InStr(tt, ",") Then
- aa = Split(tt, ",")
- For j = 0 To UBound(aa)
- For ii = 5 To 66 Step 2
- If Arr(aa(j), ii) <> "" Then
- If Arr(aa(j), ii) = "半" Then
- If Brr(i, ii - 4) = "半" Then
- Brr(i, ii - 4) = "全"
- Else
- Brr(i, ii - 4) = "半"
- End If
- Else
- Brr(i, ii - 4) = "全"
- End If
- Brr(i + 1, ii - 4) = Brr(i + 1, ii - 4) + Arr(aa(j) + 1, ii)
- End If
- Next
- Next
- Else
- For ii = 5 To 66 Step 2
- If Arr(tt, ii) <> "" Then
- If Arr(tt, ii) = "半" Then
- Brr(i, ii - 4) = "半"
- Else
- Brr(i, ii - 4) = "全"
- End If
- Brr(i + 1, ii - 4) = Arr(tt + 1, ii)
- End If
- Next
- End If
- End If
- Next
- End If
- [k7].Resize(UBound(Brr), UBound(Brr, 2)) = Brr
- End Sub
复制代码 |
|