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

¿ù»~¥N½X'9 ¸Ñ¤£¥X¨Ó ½Ð°ª¤âÀ°¦£

¿ù»~¥N½X'9 ¸Ñ¤£¥X¨Ó ½Ð°ª¤âÀ°¦£

¥»©«³Ì«á¥Ñ JeNXiU ©ó 2014-9-4 22:43 ½s¿è

Sub JamieK002()
Application.ScreenUpdating = False
Sheets("All Data").Select
Dim end1 As Long, b1 As Long, a As Long, i As Long, Rng As Range, Rng1 As Range, Wb As Workbook
i = 2
end1 = Range("D65536").End(3).Row
    With Worksheets("All Data")
    For b1 = 2 To 5
      Cells(b1, 2) = Mid(Cells(b1, 4), 1, 2)
      Cells(b1, 3) = Mid(Cells(b1, 4), 4, 5)
        If Cells(b1, 2).Value = "PD" Then
          Set Rng = Cells(b1, 3)
            Workbooks.Open Filename:="\\Wws\14.½U´¹¤G³¡\¥Í²£\¨t²ÎÀÉ\2014¨t²ÎÀÉ\" & Mid(Cells(b1, 4), 5, 1) & "¤ë\" & Cells(b1, 2) & "¨t²ÎÀÉ-" & Mid(Cells(b1, 4), 5, 1) & "¤ë" & ".XLS"
              With Worksheets("system")
              Set Rng1 = .[A:A].Find(Rng, , , xlWhole)
                If Not Rng1 Is Nothing Then
                 Rng1.Offset(0, 13).Value = Worksheets("All Data").Cells(b1, "A")
                End If
              End With
    MsgBox (Rng1)
    Workbooks(i).Close (False)
   
    End If
    Next
    End With
End Sub


¨º¦ì°ª¤â¥i¥HÀ°¦£«ü¾É ¬õ¦â¦r¨ºÃä¥X°ÝÃD
JeNXiU

Sub JamieK002()
Application.ScreenUpdating = False
Sheets("All Data").Select
Dim end1 As Long, b1 ...
JeNXiU µoªí©ó 2014-9-4 22:42

¿ù»~¥N½X'9  ªº¿ù»~»¡©ú¬O "°}¦C¯Á¤Þ¶W¥X½d³ò"
¤@¯ë·|µo¥Í³oºØ±¡§Î¬O¦]¬°¥¦§ä¤£¨ìª«¥ó,
¹ï·Ó§Aµo¥Í¦¹¿ù»~ªº«ü¥O¤§«eªº«ü¥O:
Workbooks.Open Filename:="\\Wws\14.½U´¹¤G³¡\¥Í²£\¨t²ÎÀÉ\2014¨t²ÎÀÉ\" & Mid(Cells(b1, 4), 5, 1) & "¤ë\" & Cells(b1, 2) & "¨t²ÎÀÉ-" & Mid(Cells(b1, 4), 5, 1) & "¤ë" & ".XLS"
              With Worksheets("system")
              Set Rng1 = .[A:A].Find(Rng, , , xlWhole)
                If Not Rng1 Is Nothing Then
²Ä 1 ¦æ ¶}±Ò¤F ¬Y­Ó Excel ÀÉ®× (°²³]¬° A ÀÉ®×)
²Ä 2 ¦æ ¯Á¤Þ Worksheets("system") (¦]¶}±ÒÀɮ׮ɷ|¦Û°Ê Active ¸ÓÀÉ®×, ©Ò¥H Worksheets("system") ¦b A Àɮפº)
²Ä 3 ¦æ ¦b system ¤u§@ªí§ä¥X  Rng1 Àx¦s®æ
²Ä 4 ¦æ ¦pªG¦³§ä¨ì«hÄ~Äò°õ¦æ
²Ä 5 ¦æ  Rng1.Offset(0, 13).Value = Worksheets("All Data").Cells(b1, "A") ´N¬Oµo¥Í¿ù»~ªº«ü¥O
¬ã§PÀ³¸Ó¬O¦b A Àɮפº¨S¦³ All Data ¤u§@ªí³y¦¨,
©Î³\§Aªºµ{¦¡À³¸Ó§ï¦¨ :
...
Set Wb=ThisWorkBook  ' ¥[¦¹¦æ
i = 2
...
Rng1.Offset(0, 13).Value = Wb.Worksheets("All Data").Cells(b1, "A")
...

¹ê°È¤W§Ú Debug ªº¤èªk·|¦bµo¥Í¿ù»~®ÉÂI "°»¿ù",
µM«á±N¸Ó¦æ¹ï µ¥¸¹ ¥ª¥kÃä©î¦¨¨â¦æ¦b "§Y®É¹Bºâ" °Ï¸ÕµÛ¨D­È¬Ý¬Ý:
(¨D­È®É ­Y¦³ . ¶·¥Î¨ä¤W¤è With ¥kÃ䪺ª«¥ó±a¤J, ¨Ò¦p¤W¤è¦³ With Sheets("test") ®É, .[A1] ´N­n¥Î Sheets("test").[A1] ¨Ó¨D­È)
?Rng1.Offset(0, 13).Value
»P
?Worksheets("All Data").Cells(b1, "A")
´N·|µo²{µo¥Í¿ù»~ªº¬O¸Ó¦¡¤¤ µ¥¸¹ ¥kÃ䪺³¡¤À,
±µµÛ¦A¹ï Cells(b1, "A") ¨D­È¤]¨S¦³¿ù»~ (§Y b1 «D­t¼Æ ©Î 0, ¤]¥¼¤j©ó Rows.Count)
´N¯à½T»{µo¥Í¿ù»~ªº¬O Worksheets("All Data"),
¦Ó¨ä¤¤¥²µM´N¬O All Data ¤u§@ªí¦b¦¹®Éªº ActiveWorkBook ¤¤§ä¤£¨ì.

TOP

ÁÂÁ l¤j¸ÔÁ¸ѻ¡
JeNXiU

TOP

        ÀR«ä¦Û¦b : ¤â¤ß¦V¤U¬O§U¤H¡A¤â¤ß¦V¤W¬O¨D¤H¡F§U¤H§Ö¼Ö¡A¨D¤Hµh­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD