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

[µo°Ý] °}¦Cªº­È©ñ¸m¨ìexcelªº°ÝÃD

¦^´_ 1# PKKO
³o¬O§Ú­Ó¤Hªºª½Ä±³B²z¤è¦¡¡A¶È¨Ñ§A°µ°Ñ¦Ò¥Î¡I
  1. Sub Ex2()
  2.     ' rng = [A5].CurrentRegion '°²³]UBOUND(RNG)=20 ,UBOUND(RNG,2)=10
  3.     ' Q1:  [A1].Resize(20, 10).Value = rng '§Úı±o³o¬O±N°}¦C¸ê®Æ©ñ¦bexcel«D±`§Öªº¤è¦¡,¤£¾å±oÁÙ¦³§ó§ÖªºÀ¨?
  4.     ' Q2 : ¦pªGRNG³o­Ó°}¦C,§Ú§Æ±æ¥¦¥ÑRNG(2 TO 16,3 TO 5) ©ñ¨ì [A1].Resize(15, 3).Value³o¸Ì­±¸Ó¦p¦ó°µ?¦]¬°¥Ø«eQ1µ{¦¡½XµLªk¹F¦¨³oºØ©ñ¸m¤è¦¡
  5.     ' PS:¦³¨S¦³µª®×¤£¬O°j°éªº.CELLS(x,x)=RNG(X,X)ªº¤è¦¡

  6.     Dim rng As Variant, MyArr As Variant, chars As String
  7.    
  8.     ' rng = [A30].CurrentRegion '°²³]UBOUND(RNG)=20 ,UBOUND(RNG,2)=10
  9.     rng = [A30].CurrentRegion
  10.    
  11.     [AA1].Resize(UBound(rng), UBound(rng, 2)) = rng
  12.     '  Q2 : ¦pªGRNG³o­Ó°}¦C,§Ú§Æ±æ¥¦¥ÑRNG(2 TO 16,3 TO 5)
  13.     chars = "A" & Chr(64 + 3 + 1) & (2 + 1) & ":" & "A" & Chr(64 + 3 + (5 - 3 + 1)) & (2 + (16 - 2 + 1))
  14.     MyArr = ThisWorkbook.Worksheets("¤u§@ªí1").Range(chars).Value
  15.     [AA1:AJ20].Clear
  16.    
  17.     [A1:J20].Clear
  18.     [A1].Resize(15, 3) = MyArr
  19. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2015-6-22 17:50 ½s¿è

¦^´_ 1# PKKO
¦^´_ 2# GBKEE
¨ä¹ê GBKEE ¥ý«eªº¦^ÂÐ ( #2 ) ¤]¬O¤@­Ó«Ü¤Á¹ê»Úªº½d¨Ò¡A¯÷±N¥¦²¤²¤­×§ï¦¨ PKKO ªº»Ý¨D¡G
  1. Sub EX()
  2.     Dim Rng()
  3.         
  4.     ' rng = [A5].CurrentRegion '°²³]UBOUND(RNG)=20 ,UBOUND(RNG,2)=10
  5.     ' °²³] [A30] °Ï°ì¬°¥»®×¨Ò°}¦C¤§¼È¦s³B¸Ì°Ï¶ô
  6.     ' [A30].Resize(UBound(Rng), UBound(Rng, 2)) = Rng
  7.    
  8.     chars = Chr(64 + 3 + 1) & (30 + 2) & ":" & Chr(64 + 3 + (5 - 3 + 1)) & (30 + 2 + (16 - 2))
  9.     With Range("A30").CurrentRegion
  10.         Rng = Range(chars).Value
  11.     End With
  12.     [A1].Resize(15, 3) = Rng
  13. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# c_c_lai


    C¤j±z¦n:

¤£¾å±o¤U¦C¤èªk,¦b¹ê»Ú¹Bºâ¤¤
¨º¤@ºØ³t«×³Ì§Ö©O?

1: .CELLS(X,X)=RNG(X,X)   =>°j°éªk
2: 8 ¼Ó´£¨ÑªºINDEXªk
3: ±z´£¨Ñªº¤è¦¡,¥ý±N§¹¾ã°}¦C©ñ¸m¨ìexcel,¦A¨ú¥X§Ú­nªº­È,¦A¤@¦¸©Êªº©ñ¸m¨ì­ì¥»¦ì¸m

¦]¬°§Ú¹êÅç¹L¤@¦¸©Ê©ñ¸m¤ñ°j°éªk©ñ¸mÁÙ­n§Ö³\¦h
¦ý¤£¾å±o1¡B2¡B3 ­þ¤@ºØ¤èªk·|¤ñ¸û§Ö©O?
PKKO

TOP

¦^´_ 13# PKKO
rng ¥»¨­¦s¨ú¤§¤º®e¨Ã«D¬O¥Ø«e«ü©wªº {A30}¡A¨º¥u¬O­ÓÁ|¨ÒÅý§A©ö©ó²z¸Ñ¡A
´N¦p§A¦Û­qªº RNG=[A5].CURRENTREGION ªº¦ì¸m¤@¼Ë¡C¦¹³B¡A­«ÂI¬O¦b°²³]
rng ¬O­Ó¤w¦s¦b­Èªº Variant ÅܼƭȡC§A§Æ±æ¥¦±q RNG(2 TO 16,3 TO 5)
"©â¨ú¥X¨Ó" µM«á¯à°÷±N¨ä¤º®e©ñ¸m¨ì [A1].Resize(15, 3) ªº¦ì¸m¤W¡A¦Ó¥B
"PS:¦³¨S¦³µª®×¤£¬O°j°éªº.CELLS(x,x)=RNG(X,X)ªº¤è¦¡"¡C³o¨à¨ÃµL©Ò¿×ªº
"¦A¤@¦¸©Êªº©ñ¸m¨ì­ì¥»¦ì¸m" ¤§³B²z¡C´N¦p§A¦Û¤v¥ý«eÁ|¨Ò±ý«ü©wªº [A1]¡C

«e´£¬O­n¦p¦ó¹F¦¨¦¹ºc«ä¡G
1) À³¥Î "°j°é" §¹¦¨¡H (#13 - 1 °j°éªk¡B2 INDEXªk)¡C §í©Î¬O
2) ¥ý±N rng ¤º®e¦Ü©ó¬Y°Ï¶ô¡AµM«á¦A±N¥¦¨Ì§Aªº»Ý¨D (2 TO 16,3 TO 5)
   "©â¨ú¥X¨Ó"¡A¸m©ñ¤J§A«ü©wªº [A1].Resize(15, 3) ªº¦ì¸m¤W¡C
   §¹¦¨«á¦A±N¼È¦s°Ï²M°£¡C

¦Ü©ó§ÖºCªº°ÝÃD¡Aê©ó­Ó¤H³]³Æ¤§¤£¦P¡A»Ý­n§A¦Û¦æ´ú¸Õ¨ä®t²§©Ê¡A
11#¡B12# (GBKEE) ªº¦^ÂФw¸gµ¹¤F§Aµª®×¤F¡C

TOP

¦^´_ 14# c_c_lai


    ·PÁÂC¤j,¤w¨ü¯q¨}¦h!
PKKO

TOP

¥»©«³Ì«á¥Ñ bobomi ©ó 2015-6-23 10:44 ½s¿è

¦^´_ 15# PKKO

·Q­n³t«×§Ö  ¥Î For.... Next Â^¨ú¤ù¬q  ´N¬O§Aªº³Ì¨Î¿ï¾Ü
·íµMÁÙ¦³1­Ó¤ñ For.... Next §Ö 10­¿¨ì¸U­¿ ªº¤èªk
¦ý¬O³t«×¬O¬Û¹ïªº
¯u¥¿¯Ó®Éªº¬O    CELLS(X,X)=RNG(X,X)  ³o­Ó°Ê§@

§AÂ^¨ú1¦¸´N¤@©w·|¶i¦æ1¦¸ CELLS(X,X)=RNG(X,X)  
°²³]
For .... Next ¤ù¬qÂ^¨ú°õ¦æ¦¸¼Æ   1¸U¦¸ -> ¯Ó®É 0.15 ¬í
CELLS(X,X)=RNG(X,X)   °õ¦æ¦¸¼Æ   1¸U¦¸  ->  ¯Ó®É 32 ¬í
Á`¯Ó®É = 32 ¬í + 0.15 ¬í = 32.15 ¬í

¤µ¤Ñ§A±Ë±óFor .... Next ¤ù¬qÂ^¨ú  §ï¥Î  ²Ä1§ÖªºÂ^¨úªk
Á`¯Ó®É·|Åܦ¨ = 32 ¬í + 0.01 ¬í = 32.01 ¬í
¤W­±¤w¸g´£¨ì³t«×¬O¬Û¹ïªº

32.15 ¬í ©M  32.01 ¬í  §A·|·P¨ü¨ì¼vÅT¶Ü? §¹¥þµL·P¹ï§a
©Ò¥H·Q­n§Ö ¥Î For.... Next Â^¨ú¤ù¬q  ´N¬O§Aªº³Ì¨Î¿ï¾Ü, ¥¦°_½X·|¬O²Ä2§Ö

¤£­n¬Ý¨ì¦³¦^°é°Ê§@´N»{¬°¥¦¤@©wºC  
¨ä¹ê¬Ý¤£¨ì¦^°é°Ê§@ªº, ÁÙ¬O­n¶i¦æ¦^°é°Ê§@ªº, ¥u¤£¹L³o­Ó¦^°é°Ê§@³Q¥¦¦Y¶i¥h¦b¤º³¡°õ¦æ½}¤F (Àu¶Õ¥u¦b©ó¤è«K, ´«¨Óªº±`¬O§C³t)

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD