[µo°Ý] ¦hµ§EXCEL·j´Mªº¥Îªk-©µ¦ù°ÝÃD

mark15jill

¤§«e ¦³¦b¦¹°Ï µo°Ý¹L ¦hµ§excel·j´M
©Ó»X¦U¦ì¤j¤jªºÁG®q ¦³±o¨ì¹F¨ìµª®×\
¥i¬O ²{¦b²£¥Í¤@­Ó°ÝÃD
¦]·íªì ½d¨Ò¤¤ ªº¦a§} ¥u¦³  ¥xÆW  ©M ¬ü°ê
¥i¬O¤µ¤Ñ¦pªG¦a§}¬O    ¥xÆW¥x¥_ ©ÎªÌ¬O ¬ü°ê¯Ã¬ùªº¸Ü
µ{¦¡·|µLªk§PÂ_¥X¨Ó ¶i¦Ó¥þ³¡ªÅ¥Õ
¤£ª¾¹D¬O§_¦³¤è¦¡¥i¥H¸Ñ¨M

¥H¤U¬O¨º½gªºµ{¦¡½X

1. Private Sub ¬d¸ß()
   
2. 
   
3.     Dim Text$, File$, TheSh As Object, Sh As Worksheet, Rng As Range, RngAddress$
   
4. 
   
5.     With ThisWorkbook            'µ{¦¡½X¸m©ó¬d¸ßÁ`ªí.xls
   
6. 
   
7.         Set TheSh = .Sheets("¬d¸ß")
   
8. 
   
9.         TheSh.UsedRange.Offset(2).Clear
   
10. 
    
11.         File = Dir(.Path & "\*¦~«×*.xls")
    
12. 
    
13.         Do While File <> ""
    
14. 
    
15.         With Workbooks.Open(.Path & "\" & File)
    
16. 
    
17.                 For Each Sh In .Sheets
    
18. 
    
19.                     Set Rng = Sh.Range("F:F").Find(TheSh.TextBox1, LookAt:=xlWhole)
    
20. 
    
21.                     If Not Rng Is Nothing Then
    
22. 
    
23.                         RngAddress = Rng.Address
    
24. 
    
25.                         With TheSh.Range("C" & Rows.Count).End(xlUp)
    
26. 
    
27.                             .Offset(1, -2) = File
    
28. 
    
29.                             .Offset(1, -1) = Sh.Name
    
30. 
    
31.                         End With
    
32. 
    
33.                     End If
    
34. 
    
35.                     Do While Not Rng Is Nothing
    
36. 
    
37.                         With TheSh.Range("C" & Rows.Count).End(xlUp)
    
38. 
    
39.                             .Offset(1).Resize(1, 26) = Sh.Range(Sh.Cells(Rng.Row, "A"), Sh.Cells(Rng.Row, "Z")).Value
    
40. 
    
41.                         End With
    
42. 
    
43.                         Set Rng = Sh.Range("F:F").FindNext(Rng)
    
44. 
    
45.                         If RngAddress = Rng.Address Then Exit Do
    
46. 
    
47.                     Loop
    
48. 
    
49.                 Next
    
50. 
    
51.             .Close 0
    
52. 
    
53.             End With
    
54. 
    
55.             File = Dir
    
56. 
    
57.         Loop
    
58. 
    
59.     End With
    
60. 
    
61. End Sub

½Æ»s¥N½X

ps ¦A©µ¦ù¤@­Ó°ÝÃD
¦pªG¤µ¤Ñ ­n¥Îoption±±¨î¶µ ¥h±±¨î ¥L©Ò­n·j´MªºÄæ¦ì
¦p µ{¦¡ªº·j´MÄæ¦ì¹w³] F Äæ¦ì
¦ý¬O¤µ¤Ñ¦pªG­n §ï·j´M  DÄæ¦ì
¨º»ò OPTION±±¨î¶µ³o³¡¤À­n«ç³]©w
¦³¸ÕµÛ³]©w ¦ý¬O·|Åܦ¨µLªk§PŪ(§ä¤£¥X¨Ó)

Hsieh

Set Rng = Sh.Range("D:D").Find(TheSh.TextBox1)
§ï¦¨DÄæ¼Ò½k·j´M

mark15jill

¦^´_ 2# Hsieh


    ª©¤j
§Ú§â¨º¦æµ{¦¡+¦b

                For Each Sh In .Sheets

                    ' ¨ú®ø Set Rng = Sh.Range("F:F").Find(TheSh.TextBox1, LookAt:=xlWhole)

                    Set Rng = Sh.Range("e:e").Find(TheSh.TextBox1)  '-ª©¤jªºµ{¦¡


                    If Not Rng Is Nothing Then


¥i¬O µ²ªGÁÙ¬O¤@¼Ë ªÅ¥Õ

luhpro

¦pªG¤µ¤Ñ ­n¥Îoption±±¨î¶µ ¥h±±¨î ¥L©Ò­n·j´MªºÄæ¦ì
¦p µ{¦¡ªº·j´MÄæ¦ì¹w³] F Äæ¦ì
¦ý¬O¤µ¤Ñ¦pªG­n §ï·j´M  DÄæ¦ì
¨º»ò OPTION±±¨î¶µ³o³¡¤À­n«ç³]©w
¦³¸ÕµÛ³]©w ¦ý¬O·|Åܦ¨µLªk§PŪ(§ä¤£¥X¨Ó)
mark15jill µoªí©ó 2011-6-8 15:48

¦]¬°¬Ý¤£¨ì§Aªº¹ê¨Ò, ©Ò¥H§Ú²q´ú¨º¥i¯à¬O¦]¬°§A¨S¦³µ¹©wªì­È, ©Î¬O­È¤£¥¿½T,
¥ç©Î¬O¿ï¶µ¼W§R«á·|²£¥Í¨t²Î»~§P(¤£¯à½T©w­þ¨Çºâ¤@²Õ),
¨º»ò§Ú­Ì¥i¥H¦Û¤v±±¨î¹ê»Ú°õ¦æªºµ²ªG.

¥H¤Uµ{¦¡©ñ¦b ThisWorkbook :
Private Sub Workbook_Open()
  obD = True
  obe = False
  obF = False
End Sub

¥H¤Uµ{¦¡ª½±µ¦b³]­p¼Ò¦¡¤UÂùÀ»OptionButton¥H²£¥Í¬ÛÃö¤èªk Sub ÀY§À, ¤¤¶¡©ñ¤W­Ó§O­Èªº³]©wµ{¦¡§Y¥i
Private Sub obD_Click()
  obD = True
  obe = False
  obF = False
End Sub

Private Sub dbE_Click()
  obD = False
  obe = True
  obF = False
End Sub

Private Sub obF_Click()
  obD = False
  obe = False
  obF = True
End Sub

¥u­n§â´¤¨â­Ó­«ÂI :
1. µ{¦¡¶}±Ò®É°O±o³]©w¤@­Ó OptionButton ¬° True, ¨ä¥L«h³]¬° False
2. ·í¨Ï¥ÎªÌÂIÀ»¬Y­Ó OptionButton ®ÉÀ³³y¦¨¸Ó OptionButton ¬° True, ¨ä¥L«h¶·³]¬° False
¥ç§Y¤£ºÞ¨t²Î·|¦Û°Ê§ì¨ú¨ì­þ­Ó,
§Ú­Ì¦Û¤v¨Ó«ü©w§Ú­Ì­n­þ­Ó³Q¿ï¾Ü.

mark15jill

¦^´_ 4# luhpro


    ©êºp ¬O§Úªº²¨©¿><!!
¦]¬°¦³¸ÕµÛ¥Îif optionbutton1.value = 1 then ªº¤è¦¡¤U¥h ¦ý¬O·|³y¦¨ µ{¦¡µLªk§PŪªº°ÝÃD
¤w¸gªþÀÉ ³o¬O­ì©lªºª©¥»..


¬d¸ß¤ñ¹ï-©µ¦ù°ÝÃD.rar (33.49 KB)

¤U¸ü¦¸¼Æ: 8

2011-6-9 08:04

Hsieh

¦^´_ 5# mark15jill

1. Sub ex()
   
2. Dim Ar()
   
3. Application.ScreenUpdating = False
   
4. With Sheet1
   
5. nd = IIf(.OptionButton1 = True, 2, IIf(.OptionButton2 = True, 5, 0))
   
6. mystr = "*" & .TextBox1 & "*"
   
7. If nd = 0 Then MsgBox "½Ð¿ï¾Ü¬d¸ß¶µ¥Ø": Exit Sub
   
8. fs = Dir(ThisWorkbook.Path & "\*¦~«×.xls")
   
9. Do Until fs = ""
   
10.    With Workbooks.Open(ThisWorkbook.Path & "\" & fs)
    
11.      For Each Sh In .Sheets
    
12.      With Sh
    
13.      If Application.CountA(.Columns(nd)) = 0 Then GoTo 10
    
14.         For Each a In .Columns(nd).SpecialCells(xlCellTypeConstants)
    
15.         If a Like mystr Then
    
16.            ReDim Preserve Ar(s)
    
17.            Ar(s) = Array(fs, .Name, s + 1, .Cells(a.Row, 2).Value, .Cells(a.Row, 4).Value, .Cells(a.Row, 1).Value, .Cells(a.Row, 5).Value)
    
18.            s = s + 1
    
19.         End If
    
20.         Next
    
21. 10
    
22.      End With
    
23.      Next
    
24.     .Close 0
    
25.    End With
    
26.    fs = Dir
    
27. Loop
    
28. If s > 0 Then
    
29. .[A3:G65536] = ""
    
30. .[A3].Resize(s, 7) = Application.Transpose(Application.Transpose(Ar))
    
31. Else
    
32. MsgBox "¬dµL¸ê®Æ"
    
33. End If
    
34. End With
    
35. Application.ScreenUpdating = True
    
36. End Sub

½Æ»s¥N½X

mark15jill

¦^´_ 6# Hsieh


    ª©¤j ÁÂÁ ¥i¥H¥Î¤F>< ·P®¦·P®¦
·s¼W¿ï¶µªº°ÝÃD¸Ñ¨M¤F~~
¦ý¬O ÁÙ¬O¤£À´³o¦æªº·N«ä...
nd = IIf(.OptionButton1 = True, 2, IIf(.OptionButton2 = True, 3, IIf(.OptionButton3 = True, 4, IIf(.OptionButton4 = True, 5, IIf(.OptionButton4 = True, 6, 16)))))
Áô¬ùª¾¹D»¡ true,2 ¬O ²Ä¤GÄæ¦ì  ¥i¬O ¬°¤°»ò­n¨º¼Ë¼g?? ¤£À´¤£¸³
¦p true,6,16  ¬O²Ä16­ÓÄæ¦ì  ¦ý¬O 6¬O¬Æ»ò·N«ä