- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¦^´_ 20# b9208
ªþÀÉ Ex()µ{§Ç¤£¬O 8# ªºEx()µ{§Ç- Option Explicit
- Dim D(1 To 3) As Object, ¶g¦¸ As Object, Ar ' 'Dim : ¦¹¼Ò²Õªº¨p¥ÎÅܼÆ(¶È¦¹¼Ò²Õ¥i¥Î)
- Sub EX()
- Dim i As Integer, ii As Integer, M As String, Rng As Range, ²Îp³æ¦ì As Variant
- Set D(1) = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
- Set D(2) = CreateObject("scripting.dictionary")
- Set D(3) = CreateObject("scripting.dictionary")
- Set ¶g¦¸ = CreateObject("scripting.dictionary")
- With Sheets("²Îp")
- i = Application.CountA(.[b4:b13])
- ²Îp³æ¦ì = Join(Application.Transpose(.Range(.[b4], .[b4].Offset(i - 1))), ",") '²Îp³æ¦ì=QWE,ASD
- End With
- With Sheets("©ú²Ó")
- i = 6
- Do While .Cells(i, "D") <> ""
- ' "," & ²Îp³æ¦ì & "," -> ,QWE,ASD,
- If InStr("," & ²Îp³æ¦ì & ",", "," & .Cells(i, "F") & ",") Then '¤ñ¹ï¨ì ,QWE, ,ASD, .....
- M = .Cells(i, "D") & .Cells(i, "E") & .Cells(i, "F") & .Cells(i, "L")
- If D(3)(M) = "" Then ' *** ³o¸Ì§PÂ_4Äæ³£¬Û¦P¬°¤@µ§¸ê®Æ ****
- D(3)(M) = 0
- If InStr("," & ¶g¦¸(.Cells(i, "F").Value) & ",", "," & Mid(.Cells(i, "E"), 1, 4)) & "," = 0 Then '²Îp³æ¦ì: ¤ñ¹ï¶g¦¸¤£¦s¦b, .....
- ¶g¦¸(.Cells(i, "F").Value) = IIf(¶g¦¸(.Cells(i, "F").Value) = "", "", ¶g¦¸(.Cells(i, "F").Value) & ",") & Mid(.Cells(i, "E"), 1, 4)
- End If
- M = .Cells(i, "D") & Mid(.Cells(i, "E"), 1, 4) & .Cells(i, "F")
- D(1)(M) = D(1)(M) + 1 '¥þ³¡
- M = .Cells(i, "D") & Mid(.Cells(i, "E"), 1, 4) & .Cells(i, "F") & .Cells(i, "L")
- D(2)(M) = D(2)(M) + 1 '°Ï°ì
- End If
- End If
- i = i + 1
- Loop
- End With
- With Sheets("²Îp")
- .[F:IQ].Clear
- For i = 0 To Application.CountA(.Range("B4:B13")) - 1
- Ar = Array("¥þ³¡", "³æ¦ì", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun", "¤pp")
- If i = 0 Then
- Set Rng = .[F3]
- Else
- Set Rng = .Cells(.Rows.Count, "F").End(xlUp).Offset(6) '¨C±iªí®æ¶¡¹j¤¦C
- End If
-
- ¶g¦¸(.Range("B4").Offset(i).Value) = Split(¶g¦¸(.Range("B4").Offset(i).Value), ",")
- '¨ú±o²Îp³æ¦ì¤§¶g¦¸
-
- ªí®æ»s³y Rng, .Range("B4").Offset(i)
- ªí®æ²Îp Rng.CurrentRegion
-
- For ii = 0 To Application.CountA(.Range("B18:B21")) - 1
- Set Rng = .Cells(.Rows.Count, "F").End(xlUp).Offset(6) '¨C±iªí®æ¶¡¹j¤¦C
- Ar(0) = .[B18].Offset(ii)
- ªí®æ»s³y Rng, .Range("B4").Offset(i)
- ªí®æ²Îp Rng.CurrentRegion
- Next
- Next
- End With
- End Sub
½Æ»s¥N½X |
|