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

[µo°Ý] AUTOFILL-½d³ò¤Ó¤j

[µo°Ý] AUTOFILL-½d³ò¤Ó¤j

½Ð°Ýautofill·|¦³½d³ò­­¨î¶Ü
¥Î¤U­±ªº¤è¦¡¥h¶]·|³ø¿ù»¡½d³ò¤Ó¤j
¦pªG§ï¦¨¤ñ¸û¤pªº½d³ò´N¥i¥H
ÁÙ¬O§Ú¥N½X¦³°ÝÃD¶Ü?
  1. Sub °tÀ£¥[_·l¯Ó¤ù¼Æ()
  2.     T = Timer
  3.     Application.ScreenUpdating = False
  4.     r = Range("A65536").End(xlUp).Row - 4
  5.     Range("AF4").Resize(, 8) = Array("³æ±i¤ù¼Æ", "¤ù¼Æ1", "³æ±i¤ù¼Æ", "¤ù¼Æ2", "³Ì¤j®t²§", "¦h¯Ó±i¼Æ", "¤ñ¨Ò", "¥Í²£¤ë¥÷")
  6.     With Range("AF5").Resize(, 8)
  7.         .NumberFormatLocal = "0.00_);[¬õ¦â](0.00)"
  8.         .FormulaR1C1 = Array( _
  9.             "=ROUNDDOWN(RC30/RC28,0)*ROUNDDOWN(RC31/RC29,0)", _
  10.             "=ROUNDDOWN(RC30/RC28,0)*ROUNDDOWN(RC31/RC29,0)*RC20", _
  11.             "=ROUNDDOWN(RC31/RC28,0)*ROUNDDOWN(RC30/RC29,0)", _
  12.             "=ROUNDDOWN(RC31/RC28,0)*ROUNDDOWN(RC30/RC29,0)*RC20", _
  13.             "=MAX(RC[-1],RC[-3])-RC[-24]", _
  14.             "=ROUND(RC[-1]/MIN(RC[-3],RC[-5]),2)", _
  15.             "=RC[-1]/RC20", _
  16.             "=IF(LEN(RC[-34])>9,YEAR(RC[-34])&TEXT(MONTH(RC[-34]),""00""),LEFT(RC[-34],3)+1911&RIGHT(LEFT(RC[-34],6),2))")
  17.         .AutoFill .Resize(5000, 8)
  18.     End With
  19.     With Columns("AM:AM")
  20.         .Copy
  21.         .PasteSpecial xlPasteValues
  22.     End With
  23.     Application.CutCopyMode = False
  24.    ' For C = 2 To Range("A65536").End(xlUp).Row
  25.       MsgBox Timer - T
  26. End Sub
½Æ»s¥N½X
½d³ò¤Ó¤j.rar (722.53 KB)
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

¦^´_ 14# Hsieh


    ¹ï©ó¶Wª©ªº¸Ñ¨M¤è¦¡¯u¬OÅå³Y
   ¨S·Q¹L³oºØ³B²z¤è¦¡
   §â¨º¦U5000µ§§ï¦¨°ÊºA¤@¼Ë«Ü§Ö
   ¾Ç¨ì¤@©Û
   ¯u¬O¤Ó¿³¾Ä¤F
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 1# freeffly

³o¼Ëªº¤U©Ô°Ê§@·|¨ü­­©ó¥i¥Î°O¾ÐÅé¤j¤p
¥Î°j°é¼g¤J¤½¦¡¨Ó¸Ñ¨M¦¹°ÝÃD
    Range("AF4").Resize(, 8) = Array("³æ±i¤ù¼Æ", "¤ù¼Æ1", "³æ±i¤ù¼Æ", "¤ù¼Æ2", "³Ì¤j®t²§", "¦h¯Ó±i¼Æ", "¤ñ¨Ò", "¥Í²£¤ë¥÷")
    ar = Array( _
        "=ROUNDDOWN(RC30/RC28,0)*ROUNDDOWN(RC31/RC29,0)", _
        "=ROUNDDOWN(RC30/RC28,0)*ROUNDDOWN(RC31/RC29,0)*RC20", _
        "=ROUNDDOWN(RC31/RC28,0)*ROUNDDOWN(RC30/RC29,0)", _
        "=ROUNDDOWN(RC31/RC28,0)*ROUNDDOWN(RC30/RC29,0)*RC20", _
        "=MAX(RC[-1],RC[-3])-RC[-24]", "=ROUND(RC[-1]/MIN(RC[-3],RC[-5]),2)", _
        "=RC[-1]/RC20", _
        "=IF(LEN(RC[-34])>9,YEAR(RC[-34])&TEXT(MONTH(RC[-34]),""00""),LEFT(RC[-34],3)+1911&RIGHT(LEFT(RC[-34],6),2))")
For i = 0 To 7
    With Range("AF5").Offset(, i).Resize(5000, 1)
        .NumberFormatLocal = "0.00_);[¬õ¦â](0.00)"
        .FormulaR1C1 = ar(i)
    End With
Next
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 12# mark15jill


    ®¦ ¬Ý¤Fªü¦Nª©¥Dªº©«¤l
   ¤]µo²{¦bºô¸ô¤W§ìÀÉ«Ü®e©ö¤¤¼Ð
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¼Ó¥D¤j¤j»~·|¤F
¤p§Ì¦b¥ý«e¤å³¹¦³´£¨ì  
"·Ð½ÐÀˬd±zªºEXCEL ¬O§_¦³ Startup ªº¥¨¶°¦s¦b
§Úªº³Q·P¬V¤F"

¨Ã¤£¬O»¡¼Ó¥D¤j¤j½s¼g¯f¬r

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 7# HUNGCHILIN

  ½Ð°Ýª©¥D¬O¤£¬O³o¸Ì­±¨S¦³ªF¦è´N¨S¤¤¨º¦U¬r?
    C:\Documents and Settings\freeffly\Application Data\Microsoft\Excel\XLSTART
1

µû¤À¤H¼Æ

¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 7# HUNGCHILIN
¬Ý¨ìª©¥Dªº¥t¥~¤@­Ó©«´£¨ì
¯u¬O®£©Æ
¦A¨Ó¦n¦n¬ã¨sª©¥D³o¤@©«


    °õ¦æ®ÄªG:
1.¨Ï¥Î¦¹ÀÉ ¦¹ÀÉ·|¦Û°Ê¥t¦s "StartUp.xls"ÀɮרìApplication.StartupPath ¸ô®|¸ê®Æ§¨
2.·s¼WÀÉ®×»P¶}±ÒÂÂÀɳ£·|¦Û°Ê²¾´Ó¤J¦¹·P¬V¼Ò²Õ¡A­Y¤£ª`·N§A©Ò¦³¨Ï¥Î¹LªºEXCELÀɮ׳£·|¨ü·P¬V
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 8# mark15jill


    ¤p§Ì¤£¼g¯f¬rªº
   ¤]¨S¨º¦U¥\¤O
¦^´_ 7# HUNGCHILIN
ª©¥D½Ð°Ý¦pªG¤¤¨º¦U¯f¬r¬O·|¨C¦UExcel³£·|¦³¨º¦U¥¨¶°¶Ü?
·|ÂX´²¶Ü
¤§«e³£¤£·|¥h·Qºô¸ô¤Wµo°Ýªº°ÝÃD·|¦³¯f¬r
¤£¹L¦³¨ä¥L¤H´£¥X´N°Ý¤@¤U
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

¦^´_ 7# HUNGCHILIN

¨â¦ì¤j¤j
¤§©Ò¥H¤p§Ì´£¥X¦¹ºÃ¼{¬O¦]¬° ³Ìªñ¤p§Ì³£¦bµZ¿iVB2010 «Ü¤Ö¥Î¨ìEXCEL
¥B¤W¬P´Á¤~¹q¸£­«Äé ¾ã­ÓµwºÐ²M¬~¹L
¦b¶}°_¼Ó¥D¤j¤jªºªþÀÉ«e ½T»{¹L¨S Startup ¤§¬ÛÃö¸ñ¶H
³o¤]ªþ¦X¤p§Ì¦b¤W´X¼hªº¤å³¹¤º©Ò»¡
¤@¶}©l´N¦³®É¶¡³]©w
¾É­P2GB °O¾ÐÅ骺¹q¸£¤]Àú!~

¦pªG½T©w¨S¦³  ¨º¤p§Ì¦b¦¹¸ò¼Ó¥D¤j¤j»¡Án

¹ï¤£°_ §Ú»~·|±z¤F

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¦^´_ 6# mark15jill

ªü¦N¨S¬Ý¨ìStartup

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD