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

[µo°Ý] ««ª½Äæ¦ì¶µ¥ØÂà¦Ü¤ô¥­¦CÅã¥Ü

[µo°Ý] ««ª½Äæ¦ì¶µ¥ØÂà¦Ü¤ô¥­¦CÅã¥Ü

¥»©«³Ì«á¥Ñ kasa ©ó 2015-12-23 15:25 ½s¿è

½Ð±Ð¦U¦ì¤j¤j,
¦p¦óÅýªþ¥ó¦U½s¸¹key in¤§Item¥i©ó²Ä¤G¦C¤ô¥­®i¶}©Ò¹ïÀ³¤§ItemÅã¥Ü"1"
¨Ò¦p:©ó½s¸¹3¤§BÄækey ¤J"D1"«á,¦Ó¦P¨B¥iÅý"F5"Àx¦s®æÅã¥Ü"1"
Book2.rar (7.07 KB)

C3=IF(C$2=$B3,1,"")

TOP

§Ú¸ÕµÛ¼g¥X¥H¤U¥¨¶°,µ²ªG¬O¦¨¥\ªº,¦]§ÚÁÙÄݦ۾Ǫºªì¤ßªÌ
¬G¦³¥H¤U»Ý½Ð±Ð¦U¦ì«e½å,½Ð¤£§[«ü±Ð,ÁÂÁÂ.
(1)¬O§_¦³§ó¦nªº¼gªk?
(2)¦pªGB3~B14Äæ¦ì­ì«O«ùªÅ¥Õ,­n¼g¦¨·í¤@key¤J¥N½X®É,¥i¦Û°ÊÅÜ´«¹ïÀ³Äæ¦ìÅã¥Ü"1",¸Ó¦p¦ó¹B¥Îworksheet_change»yªk¥[¤J¦¹¬q¥¨¶°
­n¥H¦p¦¹¼gªkªº¥Î·N¬O:´N¤£¥Î¦AÃB¥~¦³°õ¦æ¥¨¶°ªº°Ê§@¤F
Book2.rar (12.73 KB)

Option Explicit
Sub TESTsearch()
Dim i As Integer
For i = 3 To 14
Select Case Cells(i, 2)
       Case "A"
         Cells(i, 3) = 1
       Case "B"
         Cells(i, 4) = 1
       Case "C"
         Cells(i, 5) = 1
       Case "D1"
         Cells(i, 6) = 1
       Case "D2"
         Cells(i, 7) = 1
       Case "D3"
         Cells(i, 8) = 1
       Case "D4"
         Cells(i, 9) = 1
       Case "D5"
         Cells(i, 10) = 1
       Case "D6"
         Cells(i, 11) = 1
       Case "D7"
         Cells(i, 12) = 1
       Case "D8"
         Cells(i, 13) = 1
       Case "D9"
         Cells(i, 14) = 1
       Case "D10"
         Cells(i, 15) = 1
End Select
Next i
End Sub

TOP

·j¯Á¤Fworksheet_change»yªk,ÁÙ¬O¤£¤ÓÀ´¦p¦ó¹B¥Î

TOP

¹Á¸Õ¥Î¥H¤UWorksheet_Change¼gªk,µ²ªG·í¦bB3~B14³v¤@key¤J¥N½X«á,C3~O14Äæ¦ì¨S¤ÏÀ³,¨S©ó¹ïÀ³¥N½X³BÅã¥Ü"1"

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
If Not Intersect(Target, Range("B3:B14")) Is Nothing Then
For i = 3 To 14
Select Case Cells(i, 2)
       Case "A"
         Cells(i, 3) = 1
       Case "B"
         Cells(i, 4) = 1
       Case "C"
         Cells(i, 5) = 1
       Case "D1"
         Cells(i, 6) = 1
       Case "D2"
         Cells(i, 7) = 1
       Case "D3"
         Cells(i, 8) = 1
       Case "D4"
         Cells(i, 9) = 1
       Case "D5"
         Cells(i, 10) = 1
       Case "D6"
         Cells(i, 11) = 1
       Case "D7"
         Cells(i, 12) = 1
       Case "D8"
         Cells(i, 13) = 1
       Case "D9"
         Cells(i, 14) = 1
       Case "D10"
         Cells(i, 15) = 1
End Select
Next i
End If
End Sub

TOP

¤p§Ì¨ÓÄmÁà¤@¤U¡AÁٽЦU¦ì¤j¤j«ü±Ð...

1. ¤u§@ªí¤W·s¼W¤@­Ó CommandButton1¡A©R¦W¬°"§ó·s"
2. ¦b CommandButton1_Click ¤º¿é¤J¥H¤U¤º®e¡G

Option Explicit
Private Sub CommandButton1_Click()
Dim SH1W As Worksheet
Dim Ra1 As Range
Dim X, Y

Set SH1W = Sheets("¤u§@ªí1")
Set Ra1 = SH1W.Range("B2:B" & [B65536].End(xlUp).Row)

X = Ra1.Offset(1, 0).Resize(1, 1).Value

SH1W.Range("C3:O14").ClearContents

For Each X In Range("B3:B14")
  For Each Y In Range("C2:O2")
    If X = Y Then
      Y.Offset(X.Row - 2, Y.Row - 2).Resize(1, 1).Select
      Selection = "1"
    End If
  Next Y
Next X

End Sub

¤p§Ì¤]¬O­è¾ÇVBA¨S¦h¤[¡A¦pªG¦³¿ù¡AÁٽЦU¦ì¤j¤j¥]²[~

TOP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range, M
With Target
¡@¡@¡@If .Column <> 2 Or .Columns.Count > 1 Then Exit Sub¡@'«D²Ä¢±Äæ¡A©Î¿ï¨ú¨âÄæ¥H¤W¡A¸õ¥X
¡@¡@¡@On Error GoTo 999¡@'µo¥Í¿ù»~®É¡A°õ¦æ¼Ð°O¢¸¢¸¢¸¨º¦æµ{¦¡
¡@¡@¡@Application.EnableEvents = False¡@'Ãö³¬¨Æ¥óIJµo
¡@¡@¡@For Each xR In .Cells¡@'¾ú¹M¿ï¨ú°Ï¥þ³¡Àx¦s®æ¡]¥i¨Ï¥Î¶K¤W¦h­Ó¡^
¡@¡@¡@¡@¡@If xR.Row > 2 Then
¡@¡@¡@¡@¡@¡@xR(1, 2).Resize(1, 99).ClearContents¡@'²M°£¥k¤è­ì¦³¸ê®Æ
¡@¡@¡@¡@¡@¡@M = Application.Match(xR, [2:2], 0)¡@'§ä¥XItem¦b²Ä¢±¦Cªº¦ì¸m
¡@¡@¡@¡@¡@¡@If IsNumeric(M) Then xR(1, M - 1) = 1¡@'­Y¦³²Å¦X¡A¶ñ¤J¢°
¡@¡@¡@¡@End If
¡@¡@¡@Next
End With
999: Application.EnableEvents = True¡@'«ì´_¨Æ¥óIJµo
End Sub

TOP

¸Õ¸Õ¬Ý:
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim MH, RngB As Range, Rng2 As Range
  3.     Set RngB = [B3:B65536]    '¿é¤J°Ï
  4.     Set Rng2 = [C2:O2]        '¤ñ¹ï°Ï
  5.     If Target.Count > 1 Then Exit Sub      '¦pªG¨â®æ¥H¤W¦P®ÉChange¡A¸õ¥X
  6.     If Not Intersect(Target, RngB) Is Nothing Then   '¦pªG Target »P ¤ñ¹ï°Ï ¦³¥æ¶°
  7.         If Target = "" Then
  8.             Cells(Target.Row, "B").Resize(1, 14) = ""  '¦pªGChangeªº¬OªÅ¥Õ¡A²M°£¸Ó¦C
  9.         End If
  10.         MH = Application.Match(Target, Rng2, 0)    '¦b¤ñ¹ï°Ï§ä¥X Target ¦b²Ä2¦Cªº¦ì¸m
  11.         If IsNumeric(MH) Then          '¦p¤ñ¹ï¦¨¥\¶Ç¦^¦b¤ñ¹ïªº¦C¼Æ, §_«h¶Ç¦^¿ù»~°T®§
  12.                                        '¬G¥ý¥Î IsNumeric(MH) §PÂ_MH¬O§_¬°¼Æ¦r
  13.             Cells(Target.Row, MH + 2) = 1     '­Y¦³²Å¦X¡A¶ñ¤J¢°
  14.         End If
  15.     End If
  16. End Sub
½Æ»s¥N½X

TOP

ÁÂÁ¦U¦ì«e½å«ü¾É,§Ú¦¬¤UºCºC©CÄZ,·PÁÂ~

TOP

°w¹ï"­ã´£³¡ªL"«e½ú¼gªºµ{¦¡,½ÐÀ°¦£«üÂI¥H¤U,ÁÂÁÂ~
(1)¦b¥¼¯S§O¼g¥X©w¸qMÅܼÆÄÝ©Ê,¬O¹w³]©w¸q¬°integer¶Ü?
(2)½Ð±z«ü¾É¥H¤U¼g³o3¦æªº¯u¹ê¹ïÀ³§t¸q(ª¦¹L¤å,¦ý§Ú»{¬°§Ú¦üÀ´«DÀ´,©Ò¥H·Q·dÀ´¦b¦óºØª¬ªp¤U,¥i¦P¼Ë¹B¥Î¦¹¼gªk)
   On Error GoTo 999  'µo¥Í¿ù»~®É¡A°õ¦æ¼Ð°O¢¸¢¸¢¸¨º¦æµ{¦¡
   Application.EnableEvents = False  'Ãö³¬¨Æ¥óIJµo
   999: Application.EnableEvents = True  '«ì´_¨Æ¥óIJµo

(3)xR(1, 2)==>³o­Ó«üªº¬O? §Ú¥u·|±À½×³o¼gªk¬O¥H²Ä¤@¦C²Ä¤G¦æ¤§Àx¦s®æ¬°°ò·Ç¦ì¸m,µM«á¦³Åܰʤ§©Ò¦³Àx¦s®æ¬°xRªº¸Ü,³o¥Nªí¹ê»Ú¦³ÅÜ°Ê­ÈÀx¦s®æ¤§¦ì¸m
©ÎªÌ¬O¦]¬°¤W­±¦³¥ý¼g:For Each xR In .Cells, ©Ò¥HxR(1, 2)¬O«ü¥H²Ä¤@¦C¤G¦æ¬°°ò·Ç¦ì¸m,µM«á©T©w²Ä¤G¦æµøxR¦b²Ä´X¦C¦ì¸m¨Ó¥Nªí¹ê»Ú¦³ÅÜ°Ê­ÈÀx¦s®æ¤§¦ì¸m

TOP

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