¬d¸ß¦p¦ó°}¦C¡A¥[§Ö¤Æ¡H

s7659109

If [c1].Value = "" Then MsgBox "¶ñ¼gkey", , "´£¥Ü Exit Sub"
Sheets("search").Rows("4:10000").ClearContents
Dim rnga As Range, rw%, arr
With Sheets("search")
    For Each rnga In Sheets("data").UsedRange
    Set rnga = rnga.Find([c1])
        r = .UsedRange.Rows.Count + 1
        If Not rng Is Nothing Then
            .Cells(r, 1).Resize(1, 6).Value = Sheets("data").Cells(rnga.Row, 1).Resize(1, 6).Value
        End If
    Next
¦]¼Æ¾Ú¦³¼Æ¤dµ§¡A­Y¬d¸ßµ²ªG¼Æ¾Ú¹L¦h¡A·|¥d«Ü¤[¡A½Ð°Ý¦p¦ó¥[§Ö¡H

­ã´£³¡ªL

½Ð¤W¶ÇÀɮרû¡©ú»Ý¨D±ø¥ó¤Î³W«h~~

s7659109

»Ý¨D¡G¦b¤u§@ªísearch c1 key ¤JÃöÁä¦r¥i¥þ³¡Äæ¦ì¬d¸ß¡]±q¤u§@ªídata¡^¡A¦ýµ§¼Æ¤Öok,¦ýµ§¼Æ¤Ó¦h¡A·|¥d¡A½Ð°Ý¸Ó¦p¦ó§ï¡H

Hsieh

¦^´_ 1# s7659109

1. Sub ex()
   
2. Dim Ar(), A As Range, s&, k&
   
3. With Sheets("data")
   
4. k = .[A4].End(xlToRight).Column
   
5. For Each A In .Range(.[A5], .[A5].End(xlDown))
   
6.    mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, k))), Chr(10))
   
7.    If InStr(mystr, Sheets("search").[C1]) > 0 Then
   
8.    ReDim Preserve Ar(s)
   
9.    Ar(s) = Split(mystr, Chr(10))
   
10.    s = s + 1
    
11.    End If
    
12. Next
    
13. If s > 0 Then Sheets("search").[A3].Resize(s, k) = Application.Transpose(Application.Transpose(Ar))
    
14. End With
    
15. End Sub

½Æ»s¥N½X

s7659109

If InStr(mystr, Sheets("search").[C1]) > 0 Then
  s = s + 1
   ReDim Preserve Ar(s)
   Ar(s) = Split(mystr, Chr(10))
    End If
°ÝÃD¡G¦³¬Ý¹L s = s + 1©ñ¦b³o­Ó¦ì¸m¡A¦ý¥»¥N½X¥X²{«¬ºA¤£²Å¡A®t²§¬°¦ó¡H
¥t½Ð±Ð ­Y§ï¦¨¦bdata¤u§@ªíª½±µ¬d¸ß¡A¥H¨Æ¥ó¤è¦¡¡A°µ¨ìÁôÂÃ(¤£¬O¬d¸ß¸ê®Æ¡^¡AÁY©ñ¥\¯à¡C

Hsieh

¦^´_ 5# s7659109
§Aªº°ÝÃD¬OÅÜ°Ê°}¦C¤j¤p®É¡A±N¤W­­­È¥ý³]»P«á³]ªº®t²§
¦]¬°°}¦C°_©l­È¦b¨S¦³³]©w¤U¬O±q0¶}©l¡A­Y§A¬O±Ns=s+1©ñ¦bReDim¤§«e¡A¨º»ò°}¦C¤W­­¬O0ªº±¡ªp¤U
§A«o­nµ¹°}¦CAr(1)³]©w­È¡A³o¼Ë´N·|¥X²{¶W¥X°}¦C¯Á¤Þ½d³òªº¿ù»~
©³¤U¾A¥ÎÀx¦s®æChange¨Æ¥ó¨Ñ§A°Ñ¦Ò

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$AB$2" Then
   
3. Range("A:A").EntireRow.Hidden = False
   
4. For Each A In Range([A5], [A5].End(xlDown))
   
5.   If InStr(Join(Application.Transpose(Application.Transpose(A.Resize(, 31))), Chr(10)), [AB2]) = 0 Then A.EntireRow.Hidden = True
   
6. Next
   
7. End If
   
8. End Sub

½Æ»s¥N½X

s7659109

ÁÂÁ¡AHsiehªº¸Ñ»¡¡C