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

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

¦^´_ 35# Hsieh
¤j¤H,

¸ß°Ý§ó·sªº¹ï¸Ü®Ø¤£¦A¥X²{¤F,¦ý¥X²{¤F·sªº¹ï¸Ü®Ø¦p¤U,§Ú±N´X­ÓÀɮפW¶Ç,³Â·Ð§A.
«¬ºA¤£²Å.jpg
VBA TEST 5-answer 4_Hsieh.zip (127.44 KB)

TOP

¦^´_ 38# oobird
¦^´_ 35# Hsieh
¦Ñ¤j,½Ð°Ý§A¬O§_¦^µª§Ú²Ä37¼Óªº°ÝÃD?­Y¬Oªº¸Ü:
Âk¨s°_¨Ó¬OÀɮפӦh¤£¦P®æ¦¡©Ò³y¦¨ªº°ÝÃD,­Y¬OTOTALªº°ÝÃD,§Ú·Q«Ü¦hÄæ¦ì³£·|¥Î³o­Ó¦r,­Y§Ú²Î¤@¥u±Ä¥Î"TOTAL:"(¬O¦³«_¸¹ªº),«h¥H¤U³o2­Óµ{¦¡¤W§ÚÀ³¸Ó¦p¦ó­×§ï?¸Ó¥¦¥u¦b¬Ý¨ì"TOTAL:"ªº±¡§Î¤U¤~§@°Ê§@?(¤G­Óµ{¦¡¦U¦³¥¦¦n¥Î¥Îªº¦a¤è,§Ú³£·Q­×§ï¥¦)
  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 & "\" & "2011 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.       For Each c In .Range("A:B").SpecialCells(xlCellTypeConstants)
  17.         If c Like "TOTAL*" Then Set a = c: Exit For
  18.       Next
  19.       If Not a Is Nothing Then
  20.       Set B = a.EntireRow.Find("pcs")
  21.       Set B1 = a.EntireRow.Find("*", after:=B)
  22.       
  23.       If Not B Is Nothing Then
  24.         d(Trim(.Name) & "¼Æ¶q") = B.Offset(, -1)
  25.         c1 = a.EntireRow.Find("*", after:=B1)
  26.         d(Trim(.Name) & "ª÷ÃB") = c1
  27.       End If
  28.       End If
  29.       Set a = Nothing
  30.       End With
  31. 10
  32.     Next
  33.     ReDim Preserve Ar(y)
  34.     Ar(y) = Array(fn, d("PI¼Æ¶q"), d("PIª÷ÃB"), d("PO¼Æ¶q"), d("POª÷ÃB"), fs)
  35.     y = y + 1
  36.     .Close
  37.     d.RemoveAll
  38. End With
  39. fs = Dir
  40. Loop
  41. Sheets("Records").[A2].Resize(y, 6) = Application.Transpose(Application.Transpose(Ar))
  42. Application.ScreenUpdating = True
  43. Application.DisplayAlerts = True
  44. End Sub
½Æ»s¥N½X
  1. Sub get_value_F()
  2.     Dim a As Range, arr(1 To 5)
  3.     Application.ScreenUpdating = False    'Ãö³¬¿Ã¹õ°{Ã{
  4.     For Each a In Range([f2], [f2].End(4))    '¦bf2¥H¤Uªº¸ê®Æ½d³ò´`Àô
  5.         If Application.CountA(Rows(a.Row)) = 1 Then    'a:eÄæ¤w¦³¼g¤J¸ê®Æ´N¸õ¹L
  6.             Application.DisplayAlerts = False    'Ãö³¬¶}±Ò®Éªº¹ï¸Ü¤è¶ô
  7.             fb = ThisWorkbook.Path & "\2011 PI_PO\" & a    '±q"PI_PO¸ê®Æ§¨"¨ú¸ô®|
  8.             Set wk = GetObject(fb)    '­I´º¶}±Ò¸Ó¸ô®|ÀÉ®×
  9.             Sh = Array("PI", "PO")    '¨â­Ó¤u§@ªí¦W
  10.             On Error Resume Next    '²¤¹L¿ù»~
  11.             For s = 0 To 1
  12.                 Set mysheet = wk.Sheets(Sh(s))    '¤u§@ªíÅܶq
  13.                 If Err.Number = 0 Then    '¦p¤£µo¥Í¿ù»~(¦³³o­Ó¤u§@ªí)
  14.                 mysheet.AutoFilterMode = False '¨ú®ø¿z¿ï
  15.                     mysheet.[a:b].Replace "TOTAL:", "TOTAL", xlWhole    '§â±a¤À¸¹ªºTOTAL§ï¦¨¤£±a¤À¸¹
  16.                     r = mysheet.[a:b].Find("TOTAL", , , 1, xlByRows).Row    '¦bAB¨âÄæ´M§ä"TOTAL"
  17.                     c = mysheet.Cells(r, 15).End(1).Column    '¨úTOTAL¨º¤@¦æªº³Ì¥kÄæ(§Yª÷ÃB)
  18.                     arr(1) = Split(a, " ")(0) '¨úfÄæ²Ä¤@­ÓªÅ®æ¥H«eªº¦r¦ê
  19.                     arr(s * 2 + 2) = mysheet.Cells(r, c - 3).Value    '¨ú³Ì¥kÄæ´î3Ä檺¼Æ¦r
  20.                     arr(s * 2 + 3) = mysheet.Cells(r, c).Value    '¨ú³Ì¥kÄ檺¼Æ¦r
  21.                 End If
  22.                 Err.Clear    '²M°£¿ù»~
  23.             Next
  24.            Cells(a.Row, 1).Resize(1, 5) = arr   '¼g¤JÀx¦s®æ
  25.             Erase arr
  26.             wk.Close 0            'Ãö³¬¥´¶}ªºÀɮפ£Àx¦s
  27.         End If
  28.     Next
  29. End Sub
½Æ»s¥N½X

TOP

¦^´_ 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

¦^´_ 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 : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD