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

¦X¨ÖRangesªº¤º®e

¦X¨ÖRangesªº¤º®e

¦U¦ì¤j¤j¡G
¤p§Ì³oÃä¹J¨ì¤@­Ó°ÝÃD¡A·Q´M¨D¸Ñµª°ÝÃD¦p¤U¡G
µ{¦¡¨C¦¸¥Ñ±ø¥ó¿ï¨ú¨ìªºExcel.Ranges¼Æ¤£¦P¡A·Q¦X¨ÖRangesªº¨C¤@­Ó¤º®e­È¡A¨Ã¥Î", "¨Ó§@°Ï¹j¡A°ò¥»ªº¥i¥H¥Î¤U¦C¤èªk¡G
Dim a As Range
Dim Aranges As Range
Dim Word As String
Dim A_Array() As Variant

For Each a In Aranges
        
      Word = Word & a.Value & ", "
        
Next a

¦ý¬O·í Arangesªº¼Æ¶q«Ü¤j®É¡A¬O«Üªá®É¶¡ªº¡A©Ò¥H·Q¥Î¡uJoin¡v¦X¨Ö°}¦Cªº¤èªk¨Ó¹F¦¨¡A³t«×À³¸Ó·|§Ö¤W³\¦h¡A
A_Array=Aranges
¤W­±·|ÅýA_ArrayÅܦ¨¬O¤Gºû°}¦C¡A¦ÓJoin¥u¯à¥Î¦b¤@ºû°}¦C¤W
³o¼Ë·Q¨D±Ð¦U¦ì¤j¤j¡A¬O§_¦³¤èªk¯à±N¤Gºûª½±µÂ^¨ú¦¨¤@ºû°}¦C¡A¤£³z¹L
clio

¦^´_ 2# ikboy

·PÁÂikboyªº¦^ÂСA¨Ï¥ÎJoin, Range, Array³o¤TºØ¤èªk§Ú³£¦³´ú¸Õ¹L
¤j¬ù´£¨Ñ§Ú´ú¸Õªº®É¶¡¡AÁ`Åé¦Ó¨¥¥HJoinªº³t«×³Ì§Ö¡A¥u¬O¥L¦³¤W­­¬O65536µ§¡A¶W¹L´NµLªk¨Ï¥ÎJoin¡A¦Ó¶R100µ§¨ì65536µ§¤£¦P¼Æ¾Úªº´ú¸Õµ²ªG¡ARange¨Ï¥ÎFor each... nextªº¤èªk¡A»PArray() ªº¤è¦¡¡A¤G­Ó´ú±oªº®É¶¡¬O±µªñ¡A¥i¥H¾÷¥G»¡ªáªº®É¶¡¬O¤@¼Ëªº¼Ú¡A¥H¤W¬O§Ú¹ê»Ú´ú¸Õªº¤ß±o¡A¥i¥H¥[´î°Ñ¦Ò¡A·PÁ±z¡C
clio

TOP

¦^´_ 7# ML089

§¹¥þ¤F¸Ñ¤F¡A·PÁª©¥D­@¤ßªº±Ð¾É¡A¸U¤À·PÁÂ
clio

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-8-24 03:06 ½s¿è

¦^´_ 6# clio


Sub ex()

[A1:J10] = [row(1:10)*100+column(A:J)]
Arr = [A1:J10]

Brr = Application.Index(Arr, 1) '¨ú¤ô¥­°}¦C¡A¤@ºû°}¦C(N)
Crr = Application.Index(Arr, , 1) '¨ú««ª½°}¦C¡A¤Gºû°}¦C(Nx1)

Debug.Print Join(Brr, ",")
'Debug.Print Join(Crr, ",") '¿ù»~¡A«D¤@ºû°}¦CµLªk¨Ï¥ÎJOIN¨ç¼Æ
Debug.Print Join(Application.Transpose(Crr), ",") 'Transpose±N««ª½°}¦C(¤Gºû10x1°}¦C)Âର¤@ºû°}¦C


End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 5# ML089
²M·¡¤F¡A¥t¥~¤@­Ó°ÝÃD½Ð±Ðª©¥D

Arr = [B1:K1]
Arr = Application.Index(Arr, 0)
¯àª½±µ§âArrÅܦ¨¤@ºû°}¦C¡A¤£»Ý¦A¨Ï¥ÎTransposeÂà´«
¦ý¬O­Y
Arr = [A1:A10]
Arr = Application.Index(Arr, , 0)   
Arr¤´¬O««ª½°}¦C¡A»Ý­n¨Ï¥ÎTransposeÂà´«¦¨¤@ºû°}¦C

³o­Ó±¡ªp¤£¬O«Ü¤F¸Ñ¥Lªº¬[ºc¬°¦ó¡A¯à§_½Ðª©¤j¦Aµ¹§Ú«ü¾É¤@¤U¡A·PÁ±z
clio

TOP

¦^´_ 4# clio

VBA ªº¤@ºû¬O¤ô¥­°}¦C

[B1:K1]¬O¤ô¥­°}¦C¡AÂà¤@¦¸¬°««ª½°}¦C¡A¦AÂà¤@¦¸¬°¤ô¥­°}¦C¤~¯àÅܦ¨¤@ºû°}¦C¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 3# ML089
·PÁÂML089ª©¥Dªº¦^ÂÐ
±zªº¤èªk¸Ñ¨M¤F§Úªº§x¹Ò¡A¥u¬O¦³¤@ÂI¤£¤Ó²M·¡¡A·Q¦A¸ò±z½Ð±Ð¤@¤U
§Úµo²{[A1:A10]¡A¥ÎApplication.Transpose(Arr)´N·|Âà¥X¤@ºû°}¦C
[B1:K1]¡A¥ÎApplication.Transpose(Arr)²Ä¤@¦¸Âà¥X¤´¬O¤Gºû°}¦C¡A»Ý­n¦AÂà¤@¦¸¡A¤~·|Åܦ¨¤@ºû°}¦C
¤£¤Ó²M·¡­ì²z¬°¦ó¡H©Î¬O¦³¤°»òµ²ºc¤Wªº®t²§©O
clio

TOP

Sub ex()

    [A1:A10] = [{1;2;3;4;5;6;7;8;9;10}]
    Arr = [A1:A10]
    xj = Join(Application.Transpose(Arr), ",")
    Debug.Print xj

    [B1:K1] = [{101,102,103,104,105,106,107,108,109,110}]
    Arr = [B1:K1]
    xj = Join(Application.Transpose(Application.Transpose(Arr)), ",")
    Debug.Print xj

End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

ª½±µ¦barray¤¤´`Àô,µM«á¦A¼g¤J,¬O³Ì§Ö¡C

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD