GBKEE

¦^´_ 28# owen9399

1. Private Sub CommandButton3_Click() '¬d¸ß
   
2.     Dim I As Integer, Rng As Range
   
3.      With Sh(2)
   
4.          .AutoFilterMode = False
   
5.          For I = 1 To UBound(ar)
   
6.             If ar(I) <> "" Then .Range("A1").AutoFilter I, ar(I)
   
7.          Next
   
8.         '.Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1") ->'Sh(2)ªº AA1->AIøó
   
9.          '*******   .ColumnCount = 8    'ListBox1³]©w8øó     ©Ò¥H§ï¦¨¦p¤U********
   
10.         .Range("A:H").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
    
11.          .AutoFilterMode = False
    
12.          Set Rng = .Range("AA1").CurrentRegion.Offset(1)   'Sh(2)ªº AA1->AH :AIø󪺸ê®Æ,*****AIøó¶·¥ý§R±¼(¤â°Ê)*****
    
13.      End With
    
14.   ListBox1.RowSource = Rng.Address
    
15. End Sub
    
16. Private Sub CommandButton4_Click() '§R°£¾ã¦C
    
17.     Dim s, E As Range, I As Integer
    
18.     With ListBox1
    
19.         If .ListIndex = -1 Then MsgBox "¨S¦³¿ï¾Ü!!": Exit Sub
    
20.         s = Application.Index(Application.Transpose(Application.Transpose(.List)), IIf(.ListCount = 1, 0, 1))
    
21.         If Join(s, "") = "" Then MsgBox "¨S¦³¸ê®Æ!!": Exit Sub
    
22.         s = Application.Index(Application.Transpose(Application.Transpose(.List)), IIf(.ListCount = 1, 0, .ListIndex + 1))
    
23.         'S= ListIndexªº¿ï¾Ü.ListIndex ¨º¤@¦Cªº¸ê®Æ,·|¤ñ Resize(, 8)¦h¤@øó.
    
24.         '·í
    
25.         '->.Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1") ->'Sh(2)ªº AA1->AIøó
    
26.         '-> For Each E In .Range("A1", .Range("A1").End(xlDown)).Resize(, 8).Rows '¾ã¦C:[ ¦Û°Ê½s¸¹......·~°È­û ]
    
27.     End With
    
28.     s = Join(s, ",")   'S:  µ²¦X±±¨î¶µªº¦r¦ê [ ¦Û°Ê½s¸¹§Ç¸¹¤½¥q°tÃBµ§¼Æ¼Æ­ÈÀ³¥I¤w¥I(¶i³f¼Æ¶q1+¶i³f¼Æ¶q2)·~°È­û ]
    
29.     With Sh(2)
    
30.          For Each E In .Range("A1", .Range("A1").End(xlDown)).Resize(, 8).Rows '¾ã¦C:[ ¦Û°Ê½s¸¹......·~°È­û ]
    
31.             Debug.Print s
    
32.             Debug.Print Join(Application.Transpose(Application.Transpose(E)), ",")
    
33.             If s = Join(Application.Transpose(Application.Transpose(E)), ",") Then
    
34.                
    
35.                 If MsgBox(Join(Application.Transpose(Application.Transpose(E.Value)), ","), vbYesNo, "§R°£¦C") = vbYes Then
    
36.                      ³B¸Ì§R°£¾ã¦C E
    
37.                 End If
    
38.             End If
    
39.         Next
    
40.     End With
    
41.     CommandButton3_Click   '­«·s¬d¸ß
    
42. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 30# owen9399
½Ð¦b¹ï·Ó 29# ¬Ý¬Ý§Ú»¡¤F¤°»ò!

1. Private Sub CommandButton3_Click() 'UserForm2ªº¬d¸ßµ{¦¡½X
   
2. Dim I As Integer, Rng As Range
   
3.      With Sh(2)
   
4.           .Range("AA1").CurrentRegion = ""  '¥[³o¦æ UserForm2ªí³æ°õ¦æ¤@¦¸«á¥i¤£»Ý³oµ{¦¡½X
   
5.          .AutoFilterMode = False
   
6.          For I = 1 To UBound(ar)
   
7.             If ar(I) <> "" Then .Range("A1").AutoFilter I, ar(I)
   
8.          Next
   
9.         .Range("A:h").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
   
10. '­ì¥»¬O .Range("A:i").SpecialCells(xlCellTypeVisible).Copy .Range("AA1")
    
11.          .AutoFilterMode = False
    
12.          Set Rng = .Range("AA1").CurrentRegion.Offset(1)
    
13.      End With
    
14.   ListBox1.RowSource = Rng.Address
    
15. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 32# owen9399
¿ï¾Ü¶i³fªí¤¤ªº¸ê®Æ½d³ò,¥Î«ü¥O[¸ê®Æ]-> [¦Û°Ê¿z¿ï],±N¦U·~°È­û¨Ì¦¸ªº¿z¿ï¸ê®Æ,½Æ»s¨ì¦U·~°È­ûªº¤u§@ªí¸Ì
¦Û¤v¸Õ¸Õ,¥Î¿ý»s¥¨¶°,¬Ý¬Ýµ{¦¡½X,½m²ß¤@¤U,

GBKEE

¦^´_ 34# owen9399
¶i¨B¦n¦h,Æg.

1. Private Sub CommandButton3_Click()
   
2.     Dim y
   
3.     Dim Joken3 As String
   
4.     Joken3 = "¤p¤ý"
   
5.     If Joken3 = "" Then Exit Sub
   
6.     With Sheets("¶i³fªí")
   
7.         '­Y¦b¿z¿ï¤¤¡A¥ý¸Ñ°£Åã¥Ü¥þ³¡
   
8.         If .FilterMode Then .ShowAllData
   
9.         '¥H¡e²Ä5Äæ¡D·~°È­û¡f¶i¦æ¿z¿ï
   
10.         .[A1].AutoFilter Field:=5, Criteria1:=Joken3
    
11.         '¨ú±o¿z¿ï«á¡A³Ì«á¤@µ§¸ê®Æªº¡e¦C¸¹¡f
    
12.         y = .[A65536].End(xlUp).Row
    
13.         '¶i¦æ¶KÂà¸ê®Æ
    
14.         .Range("A1:F" & y).Copy [¤p¤ýÁ`®w¦s!A1]
    
15.         '¦A«ì´_¥þªí
    
16.         .ShowAllData
    
17.         '¨ú®ø[¦Û°Ê¿z¿ï] ¤U©Ô½b¸¹
    
18.         .[A1].AutoFilter
    
19.         MsgBox [SUM(¤p¤ýÁ`®w¦s!F:F)]  '¿z¿ï«áªº¥[Á`
    
20.   End With
    
21.   Beep
    
22. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 38# owen9399
[ªü¬üÁ`®w¦s] ¼Ò²Õ¹w³]¨Æ¥óµ{§Ç¤§µ{¦¡½X

1. Option Explicit
   
2. Private Sub Worksheet_Activate()          '¬¡­¶Ã¯: ¿ï¾Ü¤u§@ªíªº¹w³]¨Æ¥ó
   
3.     '¶i¶¥¿z¿ï  ¤½¥q¦WºÙ  'Columns.Count ->¤u§@ªíªºÁ`øó¼Æ ->³Ì«á¤@øó
   
4.     Range("B:B").AdvancedFilter xlFilterCopy, , Cells(1, Columns.Count), True
   
5.     With Range("I2").Validation   '¸ê®ÆªºÅçÃÒ²M³æ
   
6.         .Delete
   
7.         .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
   
8.         xlBetween, Formula1:="=" & Range(Cells(2, Columns.Count).Address, Cells(1, Columns.Count).End(xlDown).Address).Address
   
9.         .IgnoreBlank = True
   
10.       
    
11.     End With
    
12. End Sub
    
13. Private Sub Worksheet_Change(ByVal Target As Range)
    
14.     Application.EnableEvents = False
    
15.     If Target.Address(0, 0) = "I2" Then
    
16.         Range("J2") = [SUMIF(B:B,I2,D:D)]   '¤u§@ªíªº¨ç¼Æ¦b VBA¥Î¤¤¬A¸¹­pºâ  [ <- ¤u§@ªíªº¨ç¼Æ  -> ]
    
17.         Range("K2") = [SUMIF(B:B,I2,F:F)]
    
18.     End If
    
19.     Application.EnableEvents = True
    
20. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 41# owen9399
µ{¦¡½X§ó·s«á,½ÐÀÉ®×Ãö³¬,¦A¶}±Ò.

GBKEE

¦^´_ 44# owen9399

1. Private Function ¸ê®ÆÀˬd() As Boolean
   
2.     Dim s As String, E As Range, I As Integer, ii
   
3.     With Sh
   
4.         For I = 2 To UBound(ar)
   
5.             ii = 10 - Len(Sh.Cells(1, I))
   
6.             If I = 2 Or I = 3 Or I = 6 Then
   
7.                If ar(I).ListIndex = -1 Then s = s & IIf(s = "", "", vbLf) & Sh.Cells(1, I) + Space(ii) & vbTab & ar(I)
   
8.             Else
   
9.                 If Not IsNumeric(ar(I)) And ar(I) <> "" Then s = s & IIf(s = "", "", vbLf) & Sh.Cells(1, I) + Space(ii) & vbTab & ar(I)
   
10. 
    
11.             End If
    
12.         Next
    
13.         If s <> "" Then
    
14.             ¸ê®ÆÀˬd = True: MsgBox s, , "¸ê®Æ¦³»~!!": Exit Function
    
15.         ElseIf s = "" And ar(4) & ar(5) & ar(7) & ar(8) = "" Then
    
16.             ¸ê®ÆÀˬd = True: MsgBox "¥X³f ¶i³f ¨S¦³¼Æ¶q", , "¸ê®Æ¦³»~!!": Exit Function
    
17.         End If
    
18.         '******** ¥H¤U¬°Àˬd¬O§_¦³¬Û¦Pªº¸ê®Æ ¦p¤£»Ý­n¥i§R°£  ****************
    
19.         s = "," & Join(ar, "")
    
20.         s = Replace(s, "," & ar(1), "")  'S:  µ²¦X±±¨î¶µªº¦r¦ê [ §Ç¸¹¤½¥q²£«~¦WºÙ¥x¥_¥X³f1¥x¥_¥X³f2·~°È­û¶i³f¼Æ¶q1¶i³f¼Æ¶q2 ]
    
21.         For Each E In .Range("B1", .Range("B1").End(xlDown)).Resize(, 7).Rows
    
22.             If s = Join(Application.Transpose(Application.Transpose(E.Value)), "") Then
    
23.                 MsgBox Replace(Join(ar, ","), ar(1) & ",", "") & vbLf & "¤w¦s¦b¬° ²Ä" & E.Row - 1 & " µ§ ¸ê®Æ¤£¥i·s¼W"
    
24.                 ¸ê®ÆÀˬd = True
    
25.                 Exit Function
    
26.             End If
    
27.         Next
    
28.         '******************************************************************************
    
29.         End With
    
30.   End Function

½Æ»s¥N½X

GBKEE

¦^´_ 46# owen9399
ªþ¤WÀɮ׬ݬÝ