ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦p¦óÅý¤é´ÁÄæ¦ìªº¦~¶È¹ïÀ³¸Ó¤ë(¤w¸Ñ¨M)

[µo°Ý] ¦p¦óÅý¤é´ÁÄæ¦ìªº¦~¶È¹ïÀ³¸Ó¤ë(¤w¸Ñ¨M)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-7-11 16:20 ½s¿è

YM.rar (378.53 KB)

¦p¦óÅýComboBox1¿ï¨ì2012ªº®É­ÔComboBox2¶ÈÅã¥Ü6¤ë7¤ë
ComboBox1¿ï¨ì2013ªº®É­ÔComboBox2¶ÈÅã¥Ü8¤ë9¤ë

¦]¬°¸ê®Æ2012¦~¥u¦³6¤ë7¤ë©Ò¥H¶ÈÅã¥Ü¸Ó¦~ªº¤ë

¦^´_ 1# av8d
  1. Option Explicit          '«Å§iÅܼƪº²ßºD­n¾i¦¨
  2. Dim d As Object
  3. '°O±oDim d As Object ³o­n¸m©ó¼Ò²Õªº³»ºÝ§@¬°³o¼Ò²Õªº¨p¥ÎÅܼÆ
  4. 'ComboBox2.List = Split(d(Val(ComboBox1.Value)), ",") ¤~·|¦³µª®×
  5. Private Sub UserForm_Initialize()
  6.     Dim A, AMonth As String
  7.     Set d = CreateObject("Scripting.Dictionary")
  8.     With ActiveSheet
  9.         For Each A In .Range("a2", .[a2].End(xlDown))
  10.           If d(Year(A.Value)) = "" Then
  11.             d(Year(A.Value)) = Month(A.Value)
  12.           Else
  13.             AMonth = "," & d(Year(A.Value)) & ","                          '¦r¨åª«¥óªºitem «e«á¥[¤W ,
  14.             If InStr(AMonth, "," & Month(A.Value) & ",") = 0 Then          '¦r¨åª«¥óªºitem ¤¤¤ñ¹ï¤ë¥÷µL­«´_
  15.                 d(Year(A.Value)) = d(Year(A.Value)) & "," & Month(A.Value) '¦r¨åª«¥óªºitem ¦A¥[¤W , ¤Î¤ë¥÷
  16.             End If         
  17.           End If
  18.         Next A
  19.         ComboBox1.List = d.keys
  20.     End With
  21. End Sub
  22. Private Sub ComboBox1_Change()
  23.     If ComboBox1.ListIndex > -1 Then
  24.         TextBox9 = ComboBox1.Value
  25.         ComboBox2.List = Split(d(Val(ComboBox1.Value)), ",")
  26.         '¦r¨åª«¥óªºitem ¥ÎSplit¥H","Âà´«¦¨°}¦C
  27.     End If
  28. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ av8d ©ó 2012-7-11 16:27 ½s¿è

¦^´_ 2# GBKEE


    ½Ð°Ý¤j¤j~

¦pªG§Ú·s¼W¤F¤@­ÓComboBox3¡A¬°¤FÅã¥ÜDÄæ¡A¸Ó¦p¦ó´¡¤J?
­ì¥»¨S¦³ComboBox1¡AComboBox2ªº®É­Ô¬O³o¼Ë¤l
Private Sub UserForm_Initialize()
    Dim d As Object, A
    Set d = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        For Each A In .Range("d2", .[d2].End(xlDown))
          d(A.Value) = ""
        Next A
        ComboBox3.List = Application.Transpose(d.keys)
    End With
End Sub


¦pªG·s¼W¤@­ÓComboBox3©MComboBox4°µ·f°t¡A¸Ó¦p¦ó´¡¤J?
­ì¥»¨S¦³ComboBox1¡AComboBox2ªº®É­Ô¬O³o¼Ë¤l
Dim d As Object
Private Sub UserForm_Initialize()
    Dim A As Range, W As String
    Set d = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        For Each A In .Range("d2", .[d2].End(xlDown))
            If d(A.Value) = "" Then
                d(A.Value) = A.Offset(, 1).Value
            Else
                 W = "," & d(A.Value) & ","   '¦r¨åª«¥ó¤º®e«e«á¥[¤W ,
                If InStr(W, "," & A.Offset(, 1) & ",") = 0 Then d(A.Value) = d(A.Value) & "," & A.Offset(, 1)
                'InStr(w, "," & A.Offset(, 1) & ",") = 0 ¤ñ¹ï¦r¨åª«¥ó¤º®e¦³µLA.Offset(, 1)ªº¦r¦ê
            End If
         Next
         ComboBox3.List = d.KEYS
     End With
End Sub

TOP

¦^´_ 1# av8d


    YM2.rar (373.18 KB)

TOP

        ÀR«ä¦Û¦b : ¥Í®ð¡A´N¬O®³§O¤Hªº¹L¿ù¨ÓÃg»@¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD