Åã¥Ü²Ä¤@­Ó¦r½Ð°Ý­n¦p¦ó­×§ï¤º®e

gca78000

¸ê®Æ·j´M.rar (14.17 KB)

¦pªþ¥ó
¤U¸ü¦¸¼Æ: 5

2015-11-18 18:16

Private Sub ComboBox1_Change()
Dim A As Range
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
.Cells.Interior.ColorIndex = xlNone
For Each A In .UsedRange
  If A = ComboBox1.Text Then d(A.Address(0, 0, xlA1)) = "": A.Interior.ColorIndex = 6
Next
End With
Unload Me

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
For Each A In .UsedRange
   If A <> "" Then d(A.Value) = ""
Next
Label1.Caption = .Name
.Cells.Interior.ColorIndex = xlNone
End With
ComboBox1.List = d.keys
End Sub

«ü©w¥u±qP4·j´M¨ìAF25Àx¦s®æ¬°¤î½Ð¦U¦ì«ü±ÐÁÂÁÂ

GBKEE

¦^´_ 1# gca78000

¬O³o¼Ë¶Ü?

1. For Each A In .Range("P4:AF25")
   
2. 'For Each A In .UsedRange

½Æ»s¥N½X

gca78000

¦^´_ 1# gca78000


    ÁÂÁ¤j¤j«ü¾É¤p§Ì·Ð½Ð¦b«ü¾É¤@¤U
¦p¥u­n´MP4:P25¤ÎR4¨ìR25¤ÎT4¨ìT25¤ÎV4¨ìV25
­n¦p¦ó§ï½Ð«ü¾ÉÁÂÁÂ

gca78000

¦^´_ 3# gca78000 ©m¤ó·j´M.rar (15.52 KB)

¤U¸ü¦¸¼Æ: 1

2015-11-19 13:39

½Ð°Ý¦p¤UPrivate Sub ComboBox1_Change()
Dim A As Range
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
.Cells.Interior.ColorIndex = xlNone
For Each A In .Range("P4:P25,R4:R25,T4:T25,V4:V25")

  If A = ComboBox1.Text Then d(A.Address(0, 0, xlA1)) = "": A.Interior.ColorIndex = 6
Next
End With
Unload Me

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
For Each A In .Range("P4:P25,R4:R25,T4:T25,V4:V25")

   If A <> "" Then d(A.Value) = ""
Next
Label1.Caption = .Name
.Cells.Interior.ColorIndex = xlNone
End With
ComboBox1.List = d.keys
End Sub
P4:P25¥i·j´M¨ì¦ÓR4:R25«á«o»`´M¤£¨ì½Ð«ü¾ÉÁÂÁÂ

gca78000

¦^´_ 4# gca78000 ½Ð°Ý¦U¦ì
¦p¥u­n·j´M³æ©m¤ó­n¦p¦ó­×§ï½Ð«ü¾ÉÁÂÁÂ

gca78000

©m¤ó·j´M.rar (15.81 KB)

¤U¸ü¦¸¼Æ: 2

2015-11-19 16:42

½Ð°Ý¦U¦ì°ª¤â¦p¤U¦Cµ{¦¡½X¦pªG¥u­nÅã¥Ü²Ä¤@­Ó¦r½Ð°Ý­n¦p¦ó­×§ï¤º®e
Private Sub ComboBox1_Change()
Dim A As Range
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
.Cells.Interior.ColorIndex = xlNone
For Each A In .Range("P4:P25,R4:R25,T4:T25,V4:V25")

  If A = ComboBox1.Text Then d(A.Address(0, 0, xlA1)) = "": A.Interior.ColorIndex = 6
Next
End With
Unload Me

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Click()
End Sub

Private Sub UserForm_Initialize()
Set d = CreateObject("Scripting.Dictionary")
With ActiveSheet
For Each A In .Range("P4:P25,R4:R25,T4:T25,V4:V25")

   If A <> "" Then d(A.Value) = ""
Next
Label1.Caption = .Name
.Cells.Interior.ColorIndex = xlNone
End With
ComboBox1.List = d.keys
End Sub

GBKEE

¦^´_ 6# gca78000


¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Dim d As Object, Rng As Range  '¨p¥ÎÅܼÆ,¶È³o¼Ò²Õ¥i¥Î
   
3. Private Sub UserForm_Initialize()
   
4.     Dim A As Range, M As String
   
5.     Set d = CreateObject("Scripting.Dictionary")
   
6.     With Sheets("¬d¸ß°Ï")
   
7.         Set Rng = .Range("P4:P25,R4:R25,T4:T25,V4:V25")
   
8.         Label1.Caption = .Name
   
9.         .Cells.Interior.ColorIndex = xlNone
   
10.     End With
    
11.     For Each A In Rng
    
12.         M = Mid(A, 1, 1)
    
13.         If M <> "" Then
    
14.             If d.exists(M) Then   '¦r¨åª«¥óªºKey¦s¦b(¤w¨Ï¥Î)
    
15.                 Set d(M) = Union(A, d(M))  'Set:³]©w¬°ª«¥ó , A As Range
    
16.             Else
    
17.                 Set d(M) = A
    
18.             End If
    
19.         End If
    
20.     Next
    
21.     ComboBox1.List = d.keys
    
22. End Sub
    
23. Private Sub ComboBox1_Change()
    
24.     Rng.Interior.ColorIndex = xlNone
    
25.     d(ComboBox1.Text).Interior.ColorIndex = 6
    
26. End Sub

½Æ»s¥N½X

vbaªº»¡©ú

1. Mid ³¯­z¦¡ ±N¤@ Variant (String) ÅܼƤ¤¬Y¼Æ¶qªº¦r¤¸¥H¨ä¥L¦r¦ê¦r¤¸´À¥N¡C
   
2. 
   
3. Mid ³¯­z¦¡½d¨Ò
   
4. ¥»½d¨Ò¨Ï¥Î Mid ³¯­z¦¡±N¬Y¦r¦ê¤¤ªº´X­Ó¦r¤¸¸m´«¬°¨ä¥¦ªº¦r¤¸¡C
   
5. Dim MyString
   
6. MyString = "The dog jumps"    ' ³]©w¦r¦êªì­È¡C
   
7. Mid(MyString, 5, 3) = "fox"    ' MyString = "The fox jumps"¡C
   
8. Mid(MyString, 5) = "cow"    ' MyString = "The cow jumps"¡C
   
9. Mid(MyString, 5) = "cow jumped over"    ' MyString = "The cow jumpe"¡C
   
10. Mid(MyString, 5, 3) = "duck"    ' MyString = "The duc jumpe"¡C

½Æ»s¥N½X

gca78000

¦^´_ 7# GBKEE
·PÁª©¥D¥Î¤ß¸Ñµª¤p§Ì¨ü±Ð¤FÁÂÁª©¤j·P®¦§A