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

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

¸ê®Æ·j´M«ü©w¥ÑP4¨ìAF25Àx¦s®æ°ÝÃD

¸ê®Æ·j´M.rar (14.17 KB)
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¦ì«ü±ÐÁÂÁÂ

TOP

¦^´_ 1# gca78000

¬O³o¼Ë¶Ü?
  1. For Each A In .Range("P4:AF25")
  2. 'For Each A In .UsedRange
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 1# gca78000


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

TOP

¥»©«³Ì«á¥Ñ gca78000 ©ó 2015-11-19 13:42 ½s¿è

¦^´_ 3# gca78000 ©m¤ó·j´M.rar (15.52 KB)
½Ð°Ý¦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¤£¨ì½Ð«ü¾ÉÁÂÁÂ

TOP

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

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

TOP

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

©m¤ó·j´M.rar (15.81 KB)
½Ð°Ý¦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

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

¦^´_ 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. Mid ³¯­z¦¡½d¨Ò
  3. ¥»½d¨Ò¨Ï¥Î Mid ³¯­z¦¡±N¬Y¦r¦ê¤¤ªº´X­Ó¦r¤¸¸m´«¬°¨ä¥¦ªº¦r¤¸¡C
  4. Dim MyString
  5. MyString = "The dog jumps"    ' ³]©w¦r¦êªì­È¡C
  6. Mid(MyString, 5, 3) = "fox"    ' MyString = "The fox jumps"¡C
  7. Mid(MyString, 5) = "cow"    ' MyString = "The cow jumps"¡C
  8. Mid(MyString, 5) = "cow jumped over"    ' MyString = "The cow jumpe"¡C
  9. Mid(MyString, 5, 3) = "duck"    ' MyString = "The duc jumpe"¡C
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD