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

[µo°Ý] ¥i§_±N¦@¥Îªº¸ê®Æ®w¿W¥ß¡A¦b¤£¶}±ÒÀÉ®×±¡ªp¤U¥¿±`¨Ï¥Îvlookup¨ç¼Æ¡H

[µo°Ý] ¥i§_±N¦@¥Îªº¸ê®Æ®w¿W¥ß¡A¦b¤£¶}±ÒÀÉ®×±¡ªp¤U¥¿±`¨Ï¥Îvlookup¨ç¼Æ¡H

¦p¼ÐÃD©Ò¨¥¡ã¥i§_±N¦@¥Îªº¾Ç¥Í°ò¥»¸ê®Æ®w¿W¥ß¦¨¤@­ÓEXCELÀɮס]¾Ç®Õ¦³³]¥ß¤@­Ó±MÄݪºµwºÐ°Ï¥i¦s©ñ¤j®a¦@¥ÎÀɮס^¡A¦b¤£¶}±Ò¸ÓÀÉ®×±¡ªp¤U¡A¤´ÂÂ¥i¥H¥¿±`¨Ï¥Îvlookup¨ç¼Æ¡H

¨Ò¦p¡G§Ú·s¼W¤@­Ó¥s°µ¢¸¢¸¾Ç¦~«×´»´Á®L¥OÀç.xlsªºÀɮסA§Q¥Îvlookup¨ç¼Æ³sµ²¾Ç¥Í°ò¥»¸ê®Æ®w.xls¸Ìªº¾Ç¥Í¥X¥Í¦~¤ë¤é¤Î¨­¤ÀÃÒµ¥µ¥¸ê®Æ¡A«e´£±ø¥ó¬O¥u¶}±Ò®L¥OÀçÀɮצӾǥ͸ê®Æ®wÀɮ׫o¬O¥¼¶}±Òª¬ªp¡Avlookup¨ç¼Æ¤´ÂÂ¥i¥H¥¿±`¹B§@¡A¤£ª¾¥i§_¡H

§Ú¤§©Ò¥H³o¼ËÂÔ·V¨DÃÒ¬O¦]¬°¸ê®Æ®w¬O§Ú¦Û¦æ«Ø¥ßªº¡A¦Ó¥B§Ú·|±N©Ò¦³¦@¥Î¸ê®Æ®wªºÀÉ®×¥H¤£¦Pªº¤u§@ªí©R¦W¦s©ñ¦b¦P¤@­ÓÀɮפº¡A²z©Ò·íµM®É¶¡¤[¤FÀÉ®×·|·U¨Ó·U¤j¡A§Y¨Ï¨C¤@¾Ç´Á¬ÛÃö¬¡°Ê¤å¥ó¦Û¦¨¤@¿W¥ßÀɮסA©¹©¹¦]ÀÉ®×¹L¤j³B²z®É°¸¦Ó·|µo¥Í·í¾÷¡C¥[¤W§Oªº¦P¨Æ¤]·|¹B¥Î¨ì¾Ç¥Í°ò¥»¸ê®Æ®w¡A°ò©ó¸ê·½¦@¨É²z©À¡A¤~·|¥´ºâ±N¾Ç¥Í°ò¥»¸ê®Æ®w¿W¥ß¦s©ñ¦b¦@¥ÎµwºÐ°Ï¡C

·Ð½Ð¦³¦¹¸gÅ窺«e½ú­Ì´£¨ÑÄ_¶Q·N¥Ø¡A·P¿E¤£ºÉ¡AÁÂÁ¡I

¦^´_ 1# onegirl0204


    §Aªº·N«ä¬O»¡
­n¤@­ÓÀɮק@¬° ¬d¸ß
µM«á¨ä¥LÀɮק@¬°¸Ó Àɮתº¸ê®ÆÂ^¨ú¨Ó·½???




¥H¤U­ì©l½X ¬° ª©¤j¤§«e©Ò°µªº Â^¨ú¸Ó¤p¬q¥X¨Ó....
  1. Private Sub CommandButton1_Click() '¦¹¥\¯à«ö¶s¦WºÙ¬°  ¬d¸ß
  2. Sheets("¬d¸ß").Select

  3.     '¦¹°Ï¬° ¬d¸ß¥\¯àªºµ{¦¡½X
  4.         Sheets("¬d¸ß").Select
  5.     Range("A2:cz65535").Clear
  6. Dim Ar()
  7. Application.DisplayAlerts = False
  8. Application.ScreenUpdating = False

  9. With Sheet1


  10. nd = IIf(OptionButton1 = True, 1, IIf(OptionButton2 = True, 2, IIf(OptionButton3 = True, 3, IIf(OptionButton4 = True, 4, IIf(OptionButton5 = True, 5, IIf(OptionButton6 = True, 6, IIf(OptionButton7 = True, 8, IIf(OptionButton8 = True, 10, IIf(OptionButton9 = True, 12, IIf(OptionButton10 = True, 13, IIf(OptionButton11 = True, 14, IIf(OptionButton12 = True, 15, IIf(OptionButton13 = True, 16, IIf(OptionButton14 = True, 17, IIf(OptionButton15 = True, 25, 0))))))))))))))) '³oÃä§Ú¬O¥Î OPTIONBUTTON·í§@¬d¸ßªº¿ï¶µ±±¨î


  11. mystr = "*" & TextBox1 & "*"


  12. '---¦¹°Ï¬°§PŪ

  13. If nd = 0 Then MsgBox "½Ð¿ï¾Ü¬d¸ß¶µ¥Ø": Exit Sub
  14.   
  15. fs = Dir(ThisWorkbook.Path & "\*Á`ªí.xls") '³oÃä§ï§A­nªº¦WºÙ

  16. Do Until fs = ""

  17.    With Workbooks.Open(ThisWorkbook.Path & "\" & fs)

  18.      For Each Sh In .Sheets

  19.      With Sh

  20.      If Application.CountA(.Columns(nd)) = 0 Then GoTo 10

  21.         For Each a In .Columns(nd).SpecialCells(xlCellTypeConstants)

  22.         If a Like mystr Then

  23.            ReDim Preserve Ar(S)

  24.            Ar(S) = Array(fs, .Name, S + 1, .Cells(a.Row, 1).Value, .Cells(a.Row, 2).Value, .Cells(a.Row, 3).Value, .Cells(a.Row, 4).Value, .Cells(a.Row, 5).Value, .Cells(a.Row, 6).Value, .Cells(a.Row, 7).Value, .Cells(a.Row, 8).Value, .Cells(a.Row, 9).Value, .Cells(a.Row, 10).Value, .Cells(a.Row, 11).Value, .Cells(a.Row, 12).Value, .Cells(a.Row, 13).Value, .Cells(a.Row, 14).Value, .Cells(a.Row, 15).Value, .Cells(a.Row, 16).Value, .Cells(a.Row, 17).Value, .Cells(a.Row, 18).Value, .Cells(a.Row, 19).Value, .Cells(a.Row, 20).Value, .Cells(a.Row, 21).Value, .Cells(a.Row, 22).Value, .Cells(a.Row, 23).Value, .Cells(a.Row, 24).Value, .Cells(a.Row, 25).Value) '³oÃä¬O§PÂ_Äæ¦ì

  25.            S = S + 1

  26.            Label1.Caption = "  ¬d¸ß¦WºÙ¡G" & TextBox1.Text & "  ¡F  " & "  ¬d¸ßªºµ§¼Æ¬°¡G" & S & "  µ§¸ê®Æ"
  27.            Label2.Caption = "¬d¸ß®É¶¡¡G" & Date & "      " & Time

  28.         End If

  29.         Next

  30. 10

  31.      End With

  32.      Next

  33.     .Close 0

  34.    End With

  35.    fs = Dir

  36. Loop

  37. If S > 0 Then

  38. .[A2:z65536] = ""

  39. .[A2].Resize(S, 28) = Application.Transpose(Application.Transpose(Ar))

  40. Else
  41. MsgBox "¬dµL¸ê®Æ"
  42. End If
  43. End With

  44. Application.ScreenUpdating = True
  45. 'ActiveWindow.Close savechanges:=True
  46. End Sub
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¬Ý§O¤H¤£¶¶²´¡A¬O¦Û¤v­×¾i¤£°÷¡C
ªð¦^¦Cªí ¤W¤@¥DÃD