¦p¦ó¤À§Opºâ¥X12', 13' , 14', 15'¦~ªº¤U³æÁ`¼Æ¶q
- ©«¤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
        
|
¦^´_ 1# fangsc
¬O¸Õ¬Ý- Option Explicit
- Sub Ex()
- Dim AR, Rng(1 To 3) As Range, i As Integer, C As Integer, Sp As String
- With Sheets("¤u§@ªí1").UsedRange.Columns("B:H")
- AR = .Value
- .Offset(1, 1) = "" '
- For i = 2 To .Rows.Count ' UBound(AR) ¤]¥i¥H
- With Sheets("Data")
- .AutoFilterMode = False '¨ú®ø¦Û°Ê¿z¿ï
- C = 0
- Set Rng(1) = Sheets("¤u§@ªí1").[C1] '¦~«×
- Do While UBound(Split(Rng(1).Offset(, C), "'")) > 0
- .[a1].AutoFilter Field:=3, Criteria1:=AR(i, 1) '¦Û°Ê¿z¿ï:®Æ¸¹·Ç«h
- If .[D1].End(xlDown).Row <> .Rows.Count Then
- Sp = Split(Rng(1).Offset(, C), "'")(0)
- '¦Û°Ê¿z¿ï:¦~«×·Ç«h
- .[a1].AutoFilter Field:=5, Criteria1:=">=20" & Sp & "/1/1", Operator:=xlAnd, Criteria2:="<=20" & Sp & "/12/31"
- If .[D1].End(xlDown).Row <> Rows.Count Then '¦³¸ê®Æ
- '¥[Á`:?¦~«×¤U³æ¼Æ¶q
- AR(i, 2 + C) = Application.Sum(.Columns("D:D").SpecialCells(xlCellTypeVisible))
- Set Rng(2) = .UsedRange.SpecialCells(xlCellTypeVisible)
- If Rng(2).Areas(1).Rows.Count > 1 Then
- Set Rng(3) = Rng(2).Areas(1).Range("B1")
- Else
- Set Rng(3) = Rng(2).Areas(2).Range("B1")
- End If
- If AR(i, 6) = "" Then AR(i, 6) = Rng(3).Offset(, 3) '²Ä¤@¦¸¤U³æ¤é´Á
- If AR(i, 7) = "" Then AR(i, 7) = Rng(3) '²Ä¤@¦¸¤U³æ«È¤á
- End If
- End If
- C = C + 1
- Set Rng(1) = Rng(1).Offset(, C)
- .AutoFilterMode = False
- Loop
- End With
- Next
- .Value = AR
- End With
- End Sub
½Æ»s¥N½X |
|
|
|
|
|
|