yen956

¸Õ¸Õ¬Ý:
Q1:

1. Private Sub CommandButton1_Click()
   
2.     Dim sCel As Range
   
3.     Dim inTxt, First1 As String
   
4.     Dim LastRow As Long
   
5.     Range("K2:N" & Rows.Count & "").ClearContents    '²M°£¥ý«e·j´Mªº¸ê®Æ"
   
6.     inTxt = InputBox("½Ð¿é¤J·j´M¯Z¯Å", "·j´M¯Z¯Å")
   
7.     If inTxt = "" Then Exit Sub                      '­Y¨Ï¥ÎªÌ«ö [¨ú®ø] «hÂ÷¶}
   
8.     Set sCel = [A:A].Find(What:=inTxt, LookAt:=xlWhole)
   
9.     If sCel Is Nothing Then
   
10.         MsgBox ("¥¼§ä¨ì§A­n·j´Mªº¯Z¯Å"), vbCritical
    
11.         Exit Sub
    
12.     End If
    
13.     First1 = sCel.Address                       '«O¯d²Ä¤@­Ó·j´M¨ìªº¦ì§}
    
14.     Do
    
15.         LastRow = Cells(Rows.Count, 11).End(xlUp).Row + 1
    
16.         sCel.Resize(1, 4).Copy Cells(LastRow, 11)
    
17.         Set sCel = [A:A].FindNext(sCel)        '´M§ä¤U¤@­Ó
    
18.     Loop Until First1 = sCel.Address            '¤U¤@­Óªº¦ì¸m=²Ä¤@­Óªº¦ì¸m(¦^¨ì²Ä¤@­Óªº¦ì¸m)
    
19. End Sub

½Æ»s¥N½X

Q2:

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2.     Dim sh1, sh2 As Object
   
3.     Dim sCel As Range
   
4.     Dim First1 As String
   
5.     Dim LastRow As Long
   
6.     Set sh1 = Sheets("¤u§@ªí1")
   
7.     Set sh2 = Sheets("¤u§@ªí2")
   
8.     If Intersect(Target, sh2.[I1]) Is Nothing Then Exit Sub
   
9.     If Target = "" Then Exit Sub
   
10.     sh2.Range("H4:K" & sh2.Rows.Count & "").ClearContents    '²M°£¥ý«e·j´Mªº¸ê®Æ"
    
11.     sh1.Activate
    
12.     With sh1
    
13.         Set sCel = .[A:B].Find(What:=Target, LookAt:=xlPart)
    
14.         If sCel Is Nothing Then
    
15.             MsgBox ("¥¼§ä¨ì§A­n·j´Mªº¸ê®Æ"), vbCritical
    
16.             Exit Sub
    
17.         End If
    
18.         First1 = sCel.Address                          '«O¯d²Ä¤@­Ó·j´M¨ìªº¦ì§}
    
19.         Do
    
20.             LastRow = sh2.Cells(sh2.Rows.Count, 8).End(xlUp).Row + 1
    
21.             sCel.Resize(1, 4).Copy sh2.Cells(LastRow, 8)
    
22.             Set sCel = .[A:B].FindNext(sCel)           '´M§ä¤U¤@­Ó
    
23.         Loop Until First1 = sCel.Address               '¤U¤@­Óªº¦ì¸m=²Ä¤@­Óªº¦ì¸m(¦^¨ì²Ä¤@­Óªº¦ì¸m)
    
24.     End With
    
25.     sh2.Activate
    
26. End Sub

½Æ»s¥N½X