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

[µo°Ý] ¦p¦ó¥ÎVBAÅý¤½¦¡½Æ¨î¨ì¤u§@ªí¦³¸ê®Æªº³Ì«á¤@¦C

[µo°Ý] ¦p¦ó¥ÎVBAÅý¤½¦¡½Æ¨î¨ì¤u§@ªí¦³¸ê®Æªº³Ì«á¤@¦C

·Q½Ð°Ý¤GºØVBA¼gªk:
1.
A¦ÜCÄ榳¤½¦¡
¥Ø«e¸ê®Æ¦Ü²Ä11¦C,¸ê®Æ·|¤£Â_¼W¥[
¦p¦ó¥ÎVBAÅýA~CÄæ¦Û°Ê§â¤½¦¡½Æ¨î¨ì¤u§@ªí¦³¸ê®Æªº³Ì«á¤@¦C¡H

2.
EÄ欰µ¥®t¼Æ¦C
¦P1©Ò­z,¦p¦óÅý¼Æ¦C¦Û°Ê½Æ¨î¨ì¦³¸ê®Æªº³Ì«á¤@¦C¡H

Åý¸ê®Æ¦Û°Ê½Æ¨î¨ì¦³¸ê®Æªº³Ì«á¤@¦C¡H.rar (16.47 KB)

¦^´_ 1# PJChen

½Ð°Ý¤@¤U°w¹ï²Ä¤G­Ó°ÝÃD:
±z¬O§Æ±æEÄæ¤@ª½«ùÄò¥[1¡A¤]´N¬O488¡A489¡A490...
ÁÙ¬O´N483~487¤@ª½´`Àô?
¤£¬O«ÜÀ´½Æ»sªº·N«ä?
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 2# iamaraymond
¼Æ¦Cªº³¡¥÷¡A¤£ºÞ¨º­Ó¼Æ¦r¬O¤°»ò¡A´N±qªÅ¥Õªº¤W¤@­Ó¼Æ¦r«ùÄò+1§Y¥i

¨Ò¦p¡G
²{¦bªº¨Ò¤l¡A¦³¸ê®Æªº³¡¥÷¨ì²Ä11¦C¡A´N°w¹ïA¦ÜCÄæ¡A¦Û°Ê°»´ú¨S¤½¦¡ªº¦a¤è¶}©l½Æ²Ä¨ì²Ä11¦C¡Aµ¥ãµ¼Æ¦C¥ç¬Û¦P.

TOP

¦^´_ 3# PJChen

¤£ª¾¹D³o¼Ë²Å¤£²Å¦X±zªº»Ý¨D
  1. Sub test()

  2. lastRow1 = Cells(Rows.Count, "F").End(xlUp).Row
  3. lastRow2 = Cells(Rows.Count, "A").End(xlUp).Row
  4. If lastRow2 <> lastRow1 Then
  5. Range("A" & lastRow2 & ":" & "C" & lastRow2).AutoFill Destination:=Range("A" & lastRow2 & ":C" & lastRow1)
  6. For i = lastRow2 To lastRow1 - 1
  7.     Cells(i + 1, "E") = Cells(i, "E") + 1
  8. Next
  9. End If
  10. End Sub
½Æ»s¥N½X
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 4# iamaraymond

­º¥ý·PÁ±zÀ°¦£¼g³o­Óµ{¦¡
§Ú¦bµ{¦¡¤¤¥[¤J¤@¨Ç¨ä¥L¥\¯à¡AµM«á§âµ{¦¡»P­n°õ¦æ¦Û°Ê½Æ¨î¤½¦¡ªºÀɤÀ¶}¡A¤]§ï¤FÀɦW(¦Û°Ê½Æ¨î.xlsx¡A¤£¹LÀ³¸Ó¨S¼vÅT)¡A´ú¸Õ¤F10¦h¦¸¤@ª½¥X²{°ÝÃD(¦ý¤£¬O±zªºµ{¦¡°ÝÃD)¡A³Ì²×µo²{¦bÀɮצ۰ʶ}±Ò®É,°õ¦æµ{¦¡¨S°ÝÃD¡A¦ý­Y¬OÀÉ®×¥ý¥´¶}¤F¡A´NµLªk°õ¦æ¦Û°Ê¶ñ¼g¤½¦¡¡A³o¼Ë¤S¸Õ¤F´X¦¸¤´µM§ä¤£¥X­ì¦]¡AÀµ½ÐÀ°¦£¬Ý¤U°ÝÃD¥X¦b­þ¡H

¥t¥~§Ú§â¶ñ¼Æ¦Cªº¥\¯à¼È°±¡A¤]½Ð±Ð±z¡A­Y¬O¶ñ¼Æ¦Cªº¥\¯à¡A·Q§ï¬°±q1:1ªº¤U¤@¦C(¤]´N¬O¼ÐÃD¦Cªº¤U¤@¦C)¶}©l¡A¦Û°Ê±q1¶ñ¨ì¦³¸ê®Æ(¥HGÄ欰·Ç)ªº³Ì«á¤@¦C¡A¸Ó¦p¦ó­×§ï¡H

¥X³f¤å¥ó³sµ².rar (32.96 KB)

TOP

¥»©«³Ì«á¥Ñ iamaraymond ©ó 2018-5-14 22:38 ½s¿è

¦^´_ 5# PJChen

·Q¥ý½T»{¤@¤U±zªº°ÝÃD:
±z¬O»¡·í°õ¦æVBA«ü¥O¸Ìªºµ{¦¡®É
1.­Y¦¹®É"¦Û°Ê½Æ»s"ÃöµÛ¡A«h¥i¥H°õ¦æ©¹¤U¦Û°Ê¶ñº¡
2.­Y¦¹®É"¦Û°Ê½Æ»s"¶}µÛ¡A´N¨S¦³°Ê§@
©Ò¥H±z§Æ±æ·í"¦Û°Ê½Æ»s"¶}µÛ®É¡A¤]¥i¥H©¹¤U¦Û°Ê¶ñº¡?
¤£ª¾¹D§Úªº²z¸Ñ¥¿¤£¥¿½T

±zªº²Ä¤G­Ó°ÝÃD¬O§Æ±æ"¦Û°Ê½Æ»s"ªºE2=1¡AE3=2¡A¤@ª½¨ì¥Ø«e³Ì«á¤@¦CE11¶Ü?
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 6# iamaraymond

·í°õ¦æVBA«ü¥O¸Ìªºµ{¦¡®É
1.­Y¦¹®É"¦Û°Ê½Æ»s"ÃöµÛ¡A«h¥i¥H°õ¦æ©¹¤U¦Û°Ê¶ñº¡...Yes
2.­Y¦¹®É"¦Û°Ê½Æ»s"¶}µÛ¡A´N¨S¦³°Ê§@...Yes (¨Æ¥ý¶}±Ò¡A¥¼³z¹LVBA¥´¶}®É)
©Ò¥H±z§Æ±æ·í"¦Û°Ê½Æ»s"¶}µÛ®É¡A¤]¥i¥H©¹¤U¦Û°Ê¶ñº¡? ...YES (§Ú§Æ±æµL½×¨Æ¥ý¤w¶}±Òor¸g¥ÑVBA¶}±Ò¡A¤@«öVBA°õ¦æ¶s¡A¥¦´N¯à°õ¦æ"¦Û°Ê½Æ¨î¤½¦¡"ªº¥\¯à

±zªº²Ä¤G­Ó°ÝÃD¬O§Æ±æ"¦Û°Ê½Æ»s"ªºE2=1¡AE3=2¡A¤@ª½¨ì¥Ø«e³Ì«á¤@¦CE11¶Ü?...YES
  1.     For Each Wb In Workbooks
  2.         If UCase(Wb.Name) = UCase("¦Û°Ê½Æ¨î.xlsx") Then
  3.             Msg = True  'Àɮפw¶}±Ò
  4.             Exit For
  5.         End If
  6.     Next
  7.     '*****************************************
  8.     If Msg = True Then 'Àɮפw¶}±Ò
  9.         Set Wb = Workbooks("¦Û°Ê½Æ¨î.xlsx")
  10.     Else               'Àɮש|¥¼¥´¶}®É
  11.         Set Wb = Workbooks.Open("W:\00_¬ì¼Ý\¥X³f¤å¥ó³sµ²\¦Û°Ê½Æ¨î.xlsx")
  12.     End If
½Æ»s¥N½X

TOP

¦^´_ 7# PJChen

³o¼Ë¶Ü?
  1. With Wb.Sheets("­q³æ¥X³f")
  2.    
  3.     lastRow1 = .Cells(Rows.Count, "G").End(xlUp).Row  '¥HGÄ檺¸ê®Æ¦C¬°°ò·Ç
  4.     lastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row '¥HAÄ檺¸ê®Æ¦C¬°°ò·Ç
  5.     lastRow3 = .Cells(Rows.Count, "BL").End(xlUp).Row '¥HBLÄ檺¸ê®Æ¦C¬°°ò·Ç
  6.     lastRow4 = .Cells(Rows.Count, "BP").End(xlUp).Row '¥HBPÄ檺¸ê®Æ¦C¬°°ò·Ç
  7.    
  8.     If lastRow2 <> lastRow1 Then
  9.     .Range("A" & lastRow2 & ":" & "C" & lastRow2).AutoFill Destination:=.Range("A" & lastRow2 & ":C" & lastRow1)  '·íAÄæ³Ì«á¤@¦C¤£µ¥©óGÄæ®É,A:C¦Û°Ê©¹¤U¶ñº¡

  10.     For i = 2 To lastRow1
  11.     .Cells(i, "E") = i - 1
  12.     Next
  13.     End If
  14.    
  15.     If lastRow3 <> lastRow1 Then
  16.     .Range("BL" & lastRow3 & ":" & "BN" & lastRow3).AutoFill Destination:=.Range("BL" & lastRow3 & ":BN" & lastRow1)  '·íBLÄæ³Ì«á¤@¦C¤£µ¥©óGÄæ®É,¦Û°Ê©¹¤U¶ñº¡
  17.    
  18.     End If

  19.     If lastRow4 <> lastRow1 Then
  20.     .Range("BP" & lastRow4 & ":" & "CX" & lastRow4).AutoFill Destination:=.Range("BP" & lastRow4 & ":CX" & lastRow1)  '·íBPÄæ³Ì«á¤@¦C¤£µ¥©óGÄæ®É,¦Û°Ê©¹¤U¶ñº¡
  21.    
  22.     End If

  23. End With
½Æ»s¥N½X
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

¦^´_ 8# iamaraymond

·PÁÂ...³o¼Ë§¹¥þ¥¿±`¤F¡C

TOP

¦^´_ 9# PJChen
¨ä¹ê¥u¬OÀ°±z¥[­Ó"."¦Ó¤wXD
¥Îwithªº®É­Ô­n¨Ï¥Î"."¤~¥i¥H¨Ï¥Î¸Óª«¥ó
Excel VBAºô­¶¸ê®Æ¦¬¶°±Ð¾Ç:
http://forum.twbts.com/thread-20848-1-1.html

TOP

        ÀR«ä¦Û¦b : ¦³®É·í«äµL®É­W¡A¦n¤Ñ­n¿n«B¨Ó³¡C
ªð¦^¦Cªí ¤W¤@¥DÃD