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

[µo°Ý] ¦p¦ó¶×¤JEXCEL¸ê®Æ?

¦^´_ 40# Hsieh
¤j¤H,
Àɮפ¤ª÷ÃB©Î¼Æ¶q­Y¥X²{        ¿ù»~°T®§,¨Ò:#REF!,¦p¦óÅý¥¦¨ÌµM¶ñ¦bPI_PO RecordsÀɮפ¤?§Ú¨Æ«á¦A¥h¬d¬Ý°ÝÃD´N¦n¤F,§_«hµ{·|°±º¢¤£°õ¦æ!

¦^´_ 38# oobird
oobird¤j¤j:
¦]¬°±z¼gªºµ{¦¡§¹¥þ¤£·|Åã¥Ü¥¿¦brun­þ¤@µ§,©Ò¥H¨C·í¥X²{¥H¤Uªº¹ï¸Ü®Ø,§Ú¯u¤£ª¾¬O¤°»ò°ÝÃD?¥B¦]¬°¦³«Ü¦hÀɮצ³³oºØ±¡§Î(§ÚµLªk±oª¾¬O­þ¤@­Ó?),¯à§_´N¥u¬OÅýµ{¦¡¦Û°Ê¿ï¾Ü¤£­n§ó·s¥B¦Û°Ê©¹¤U°õ¦æ?§_«hµ{¦¡¦brun®É¥²¶·¤@ª½µ¥¦b¹q¸£«e«ö"¤£§ó·s"!

TOP

Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
³o¨â¦æ¤@°_¥Î¸Õ¸Õ

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-5-19 14:49 ½s¿è

¦^´_ 41# PJChen
  1. Sub get_value()
  2. Dim Sh As Worksheet, a As Range, Ar(), B As Range, B1 As Range
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Application.ScreenUpdating = False
  5. Application.DisplayAlerts = False
  6. fd = ThisWorkbook.Path & "\" & "PI_PO\"
  7. fs = Dir(fd & "*xls*")
  8. Do Until fs = ""
  9. With Workbooks.Open(Filename:=fd & fs, UpdateLinks:=False)
  10. n = Split(fs, " ")(0)
  11. s = InStr(n, "BCM") + 3
  12. fn = Mid(n, s)
  13.    For Each Sh In .Sheets
  14.       With Sh
  15.       If Trim(.Name) <> "PO" And Trim(.Name) <> "PI" Then GoTo 10
  16.       ay = .UsedRange.Value
  17.       For i = 1 To UBound(ay)
  18.          mystr = ""
  19.          For j1 = 1 To UBound(ay, 2)
  20.             mystr = IIf(mystr = "", UCase(Trim(.Cells(i, j1).Text)), mystr & Chr(10) & UCase(Trim(.Cells(i, j1).Text)))
  21.          Next
  22.          If Trim(Replace(mystr, Chr(10), "")) Like "TOTAL*PCS?*" Then
  23.          ak = Split(mystr, "PCS")
  24.          ax = Split(Trim(ak(0)), Chr(10))
  25.          ap = Split(Trim(ak(1)), Chr(10))
  26.                d(Trim(.Name) & "¼Æ¶q") = ax(UBound(ax) - 1)
  27.                d(Trim(.Name) & "ª÷ÃB") = ap(2)
  28.                Exit For
  29.          End If
  30.       Next
  31.       End With
  32. 10
  33.     Next
  34.     ReDim Preserve Ar(y)
  35.     Ar(y) = Array(fn, d("PI¼Æ¶q"), d("PIª÷ÃB"), d("PO¼Æ¶q"), d("POª÷ÃB"), fs)
  36.     y = y + 1
  37.     .Close
  38.     d.RemoveAll
  39. End With
  40. fs = Dir
  41. Loop
  42. Sheets("Records").[A2].Resize(y, 6) = Application.Transpose(Application.Transpose(Ar))
  43. Application.ScreenUpdating = True
  44. Application.DisplayAlerts = True
  45. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 42# oobird

¤j¤j,
ªGµM¤£·|¦A¥X²{¸ß°Ýªº¹ï¸Ü®Ø¤F.

TOP

¦^´_ 43# Hsieh

¦Ñ¤j,

40¼Óªºµ{¦¡§ì¨ìªº¸ê®Æ¤ñ¸û§¹¾ã.
³o¦¸­×§ïªºÁöµM¥i¥H±N#REF! ³oºØÀx¦s®æªº¸ê®Æ¨ÌµM¶ñ¤JPI_PO Records¤¤¤£·|¦A°±º¢¤£°õ¦æ,¦ý¦³¨ä¥L¥H©¹§ì±o¨ìªº¸ê®Æ²{¦b«oµLªk§ì¨ì!
¯à§_¥H40¼Óªºµ{¦¡¬°°ò¦,¥u­n¤p¤pªº­×§ï¤£·|¦]¬°¶Ã½Xªº¼Æ­È?(¦p«e­±©Ò´£#REF! )¦Ó°±º¢¤£°õ¦æ?

TOP

        ÀR«ä¦Û¦b : ¬°¦Û¤v§äÂǤfªº¤H¥Ã»·¤£·|¶i¨B¡C
ªð¦^¦Cªí ¤W¤@¥DÃD