- ©«¤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# yagami12th - Option Explicit
- Sub Ex()
- Dim E As Worksheet, i As Date, M As Variant, AR(), C As Variant
- ReDim AR(1 To 5, 1 To 1) '²Ä¤@ºû ¦³5Ó¤¸¯À ,²Ä¤Gºû ¦³1Ó¤¸¯À
- AR(1, 1) = "¤é´Á"
- AR(2, 1) = "¶RÅv ³Ì¤j¥¼Ü¶q"
- AR(3, 1) = "¶RÅv ³Ì¤j¥¼¥Ü¶q¸¨¦bþÓ¼i¬ù»ù"
- AR(4, 1) = "½æÅv ³Ì¤j¥¼Ü¶q"
- AR(5, 1) = "½æÅv ³Ì¤j¥¼¥Ü¶q¸¨¦bþÓ¼i¬ù»ù-"
- Application.ScreenUpdating = False
- For Each E In Sheets '¤u§@ªí1,¤u§@ªí2,¤u§@ªí3
- If E.FilterMode Then E.AutoFilterMode = False '¦³¿z¿ï®É ¨ú®ø[¿z¿ï]
- For i = E.[A2] To E.[A2].End(xlDown) '¤é´Á[A2]ª½¨ì³Ì«áªº¤é´Á
- E.AutoFilterMode = False
- E.Range("A1").AutoFilter 1, i
- If E.Range("A1").End(xlDown).Row <> Rows.Count Then '¦]¤¤¶¡¦³¨S¥æ©ö¤é´Á[¿z¿ï]¤£¨ì¸ê®Æ
- ReDim Preserve AR(1 To 5, 1 To UBound(AR, 2) + 1) '²Ä¤Gºû 즳¤¸¯À¦A¥[1¤¸¯À
- AR(1, UBound(AR, 2)) = i '¤é´Á: i
- For Each C In Array("¶RÅv", "½æÅv")
- E.AutoFilterMode = False
- E.Range("A1").AutoFilter 1, i
- E.Range("A1").AutoFilter 5, C
- M = Application.Max(E.Range("L:L").SpecialCells(xlCellTypeVisible))
- AR(IIf(C = "¶RÅv", 2, 4), UBound(AR, 2)) = M '³Ì¤j¥¼Ü¶q
- 'IIf(C = "¶RÅv", 2, 4) C = "¶RÅv"->2 ,C <> "¶RÅv"-> 4
- Set M = E.Range("L:L").SpecialCells(xlCellTypeVisible).Find(M, LookIn:=xlValues)
- AR(IIf(C = "¶RÅv", 3, 5), UBound(AR, 2)) = M.Offset(, -8) '³Ì¤j¥¼¥Ü¶q¸¨¦bþÓ¼i¬ù»ù
- Next
- End If
- Next
- Next
- With Sheets.Add(Sheets(1)) '·s¼W¤u§@ªí
- .[A1].Resize(UBound(AR, 2), UBound(AR)) = Application.WorksheetFunction.Transpose(AR)
- .Cells.EntireColumn.AutoFit
- End With
- Application.ScreenUpdating = True
- End Sub
½Æ»s¥N½X |
|