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

[µo°Ý] VBA½Æ»sÀx¦s®æÃC¦â

[µo°Ý] VBA½Æ»sÀx¦s®æÃC¦â

¦U¦ì¦n¡A¦³­Ó°ÝÃD³Â·Ð¦U¦ìÀ°¦£¸Ñµª
¥Ø«e¨â­ÓEXCELÀÉ¡A¤@­Ó¥Î¨Ó§@¥X¶Ôµn°O¡A¤@­Ó¥X¶Ô²Î­p¥Î
²Î­pªº³¡¤À¬O¨Ì·Ó©Ò»Ý­nªº¤é´Á°Ï¶¡°µ§ì¨ú¸ê®Æ¡A¦ý¦]¬°µn°Oªº³¡¤À¦h¼W¥[¤FÀx¦s®æÃC¦â¨Ó³Æµù¡A
§Æ±æ§ì¨ì²Î­p¨ºÃä®ÉÀx¦s®æÃC¦â¤]¯à¤@¨Ö¹L¥h
  1. Sub Main()
  2.     Call ¤é´Á°Ï¶¡(S, E)
  3.     Call ¥[¸É¥ð(S, E)
  4.     Call ¥[¯Z¶O(S, E)
  5.    
  6. End Sub

  7. Sub ¤é´Á°Ï¶¡(S, E)
  8.    
  9.     S = CDate(Sheets("³]©w­¶").Range("b8"))
  10.     E = CDate(Sheets("³]©w­¶").Range("c8"))
  11.   
  12. End Sub


  13. Sub ¥[¸É¥ð(S, E)

  14.     'Call «Ø¥ß·s¤u§@ªí(S, E)

  15.     i = 3
  16.     x = 2
  17.    
  18.     If Sheets(1).Cells(2, 1) = "" Then
  19.     x = 2
  20.     Else
  21.     x = ActiveSheet.UsedRange.Rows.Count + 1
  22.    ' MsgBox x
  23.     End If
  24.          
  25.     SNAME = Sheets("³]©w­¶").Range("B2") '¬¡­¶Ã¯¦WºÙ

  26. Do While Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 1) <> ""
  27.     'MsgBox S
  28.     If (Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 4) >= CDate(S) And Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 4) <= CDate(E)) = True Then
  29.          '«Ø¥ß·s¤u§@ªí
  30.          
  31.          Sheets(1).Cells(x, 1) = Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 1)
  32.          Sheets(1).Cells(x, 2) = Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 2)
  33.          Sheets(1).Cells(x, 3) = Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 12)
  34.          Sheets(1).Cells(x, 4) = CDate(Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 4))
  35.          Sheets(1).Cells(x, 8) = Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 5)

  36.          x = x + 1
  37.     End If
  38. i = i + 1
  39. Loop

  40. End Sub

  41. Sub ¥[¯Z¶O(S, E)

  42.     'Call «Ø¥ß·s¤u§@ªí(S, E)

  43.     i = 3
  44.     x = 2
  45.    
  46.     If Sheets(1).Cells(2, 1) = "" Then
  47.     x = 2
  48.     Else
  49.     x = ActiveSheet.UsedRange.Rows.Count + 1
  50.     'MsgBox x
  51.     End If
  52.          
  53.     SNAME = Sheets("³]©w­¶").Range("B2") '¬¡­¶Ã¯¦WºÙ

  54. Do While Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 1) <> ""
  55.      If Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 3) <> "" And Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 4) <> "" = True Then
  56.             
  57.          Sheets(1).Cells(x, 1) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 2)
  58.          Sheets(1).Cells(x, 2) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 3)
  59.          Sheets(1).Cells(x, 3) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 1)
  60.          Sheets(1).Cells(x, 4) = CDate(Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 4))
  61.          Sheets(1).Cells(x, 5) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 5)
  62.          Sheets(1).Cells(x, 6) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 6)
  63.          Sheets(1).Cells(x, 7) = Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 7)

  64.          x = x + 1
  65.          End If
  66.          i = i + 1
  67. Loop


  68. End Sub
½Æ»s¥N½X

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-20 00:46 ½s¿è

¦^´_ 1# ÅÚ½³ªd

¨SÀÉ®×´ú¸Õ¡A§ÚÁ¿¤èªk¡A½Ð¦Û¤v§ï

¥Î"Copy" ¨ú¥N "="  ªº¤è¦¡¡A¥i¥H§âÀx¦s®æ®æ¦¡¤@¨ÖCopy¹L¥h


Sheets(1).Cells(x, 1) = Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 1)

§ï¦¨

Workbooks(SNAME).Sheets("¸É¥ð").Cells(i, 1).Copy Sheets(1).Cells(x, 1)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¦^´_ 2# n7822123


  ¦n¡AÁÂÁÂ

TOP

¦^´_ 2# n7822123


  §A¦n¡A½Ð°Ý§Ú°µ§ó§ï·|¦b¤é´Á¨º¦æ·|¥X²{¿ù»~¡A¸Ó«ç§ó§ï©O¡H
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 2).Copy Sheets(1).Cells(x, 1)
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 3).Copy Sheets(1).Cells(x, 2)
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 1).Copy Sheets(1).Cells(x, 3)
         CDate(Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 4)).copy Sheets(1).Cells(x, 4)
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 5).Copy Sheets(1).Cells(x, 5)
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 6).Copy Sheets(1).Cells(x, 6)
         Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 7).Copy Sheets(1).Cells(x, 7)

TOP

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-5-28 02:17 ½s¿è

¦^´_ 4# ÅÚ½³ªd


¬Ý¬Ý¬O¤£¬O§A­nªº

Workbooks(SNAME).Sheets("¥[¯Z¤Î½Ð°²").Cells(i, 4).copy Sheets(1).Cells(x, 4)  
Sheets(1).Cells(x, 4).NumberFormatLocal = "hh:mm:ss AM/PM"



CDate(¼Æ­È) ---------  O
CDate(ª«¥ó) ---------  X


Àx¦s®æª«¥ó.Copy ------- O
­È.Copy   ----------------X


Àx¦s®æ¬O­Óª«¥ó
Copy ¬OÀx¦s®æª«¥óªº ¤èªk
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD