[µo°Ý] ¸ê®ÆÂà¸m¶K¤W

ÅÚ½³ªd

¦U¦ì¤j¤j¦n

¦]¤u§@¤W»Ý­n°µ¨ìñ¨ìªí¡A¦ý¦]¨Ó·½®æ¦¡³£¤£¬Û¦P©Ò¥H¨C¦¸³£ªá«Ü¦h¶¡¦b°µ¶K¤W­È&Âà¸mªº°Ê§@

§Ú¥Ø«e¬O¥Î³Ì²Âªº¤èªk¥ý±q¨BÆJ¤@¶K¦¨¨BÆJ¤G¡AµM«á¦AÂন¨BÆJ¤T
¤£ª¾¹D¯à¤£¯à¼g¦¨¥¨¶°¡A·Q½Ð¦U¦ì¤j¤jÀ°¦£

­ã´£³¡ªL

Sub ¸ê®ÆÂà¸m¶K¤W()
Dim Arr, Brr, i&, j&, T$, R&, C%
Range("a13:g2000").ClearContents
If [k13] <> "" Then Arr = Range([k13], [k65536].End(xlUp)(2))
If [n13] <> "" Then Arr = [n13].CurrentRegion
ReDim Brr(1 To 2000, 1 To 7)
If Not IsArray(Arr) Then Brr(1, 1) = Arr: GoTo 999  '­Y¸ê®Æ¥u¦³¤@­Ó~~ For j = 1 To UBound(Arr, 2)
For i = 1 To UBound(Arr)
        T = Arr(i, j): If T = "" Then GoTo i01
        C = C + 1: Brr(R + 1, C) = T
        If C = 7 Then C = 0: R = R + 1
i01: Next i
Next j
999: [a13].Resize(R + 1, 7) = Brr
End Sub

samwang

½Ð°ÝVBA¬O­þ­Ó¬ì¨t·|¾Ç¨ì©O¡H
§Ú¥H«e¨S¾Ç¹L¥u¬O¤u§@¤W»Ý­n¡A¥Ø«e§Ú³£¬O¦Û¤v¶R®Ñ¦^¨Ó¾Ç²ß¡A
«ÜÃø¾Ç·|«ç ...
ÅÚ½³ªd µoªí©ó 2021-11-25 13:37

§Úª¬ªp¤]¬O©M±z¤@¼Ë¡A¦]¬°¤u§@»Ý­n¶R®Ñ¦^¨Ó¬Ý¡A¦ý¬O­nÀ³¥Î´N¦³ÂIÃø«×
³Ì«á¦Û¤v¦b¦¹ºô¯¸¬Ý¨ä¥L«e½úªº¼gªk«á¡A¦Û¤v­n¹Á¸Õ¼g¡B¦h½m²ß¡B¹J¨ì°ÝÃD¦A¸ß°Ý
¥[ªo ¥[ªo¡AÁÂÁÂ

samwang

¤£·|¨â­Ó°Ï°ì³£¦³¸ê®Æ¡A¥u·|¦³¨ä¤¤¤@­Ó
ÅÚ½³ªd µoªí©ó 2021-11-25 13:34

½Ð¦A¸Õ¬Ý¬Ý¡AÁÂÁÂ
Sub test2()
Dim Arr, Brr(1 To 1000, 1 To 3), Crr()
Dim i&, j&, n%, s%, m%, R%
If [n13] <> "" Then
    Arr = [n13].CurrentRegion  '¨Ó·½¸ê®Æ1
    For j = 1 To UBound(Arr, 2): For i = 1 To UBound(Arr)
        If Arr(i, j) <> "" Then
            If n < 7 Then n = n + 1 Else n = 1
            s = s + 1: Brr(s, 1) = n
            Brr(s, 2) = Arr(i, j): Brr(s, 3) = s
        End If
    Next i: Next j
ElseIf [k13] <> "" Then
    Arr = Range([k13], [k65536].End(3)) '¨Ó·½¸ê®Æ2
    For i = 1 To UBound(Arr)
        If Arr(i, 1) <> "" Then
            s = s + 1: Brr(s, 2) = Arr(i, 1)
        End If
    Next
End If

R = Int(s / 7) + 1: ReDim Crr(1 To R, 1 To 7): k = 1
For i = 1 To s
    For j = 1 To 7
        m = m + 1: If m > s Then GoTo 99
        Crr(i, j) = Brr(m, 2)
    Next
99: Next i
Range("a13").Resize(R, 7) = Crr  'Âà¶K¨ì3
End Sub
   

ÅÚ½³ªd

¦^´_  ÅÚ½³ªd


  1.Ãö©ó&©Î$»P%¡AVBA®Ñ¤W¨S¥X²{¹L³o­Ó¡A¤£¾å±o¬O«ü¤°»ò·N«ä >>¥i¤Wºô§ä¤@¤U´N¦³¡A¨º¨Ç ...
samwang µoªí©ó 2021-11-25 12:27

½Ð°ÝVBA¬O­þ­Ó¬ì¨t·|¾Ç¨ì©O¡H
§Ú¥H«e¨S¾Ç¹L¥u¬O¤u§@¤W»Ý­n¡A¥Ø«e§Ú³£¬O¦Û¤v¶R®Ñ¦^¨Ó¾Ç²ß¡A
«ÜÃø¾Ç·|«çÀ³¥Î¡A¦Ó¥B«n³¡«Ü¤Ö¦³³oºØ½Ò¥i¥H¤W¡A
¬O§_¦³±ÀÂ˪º®ÑÄy¥i¥H¬Ý©O¡H

ÅÚ½³ªd

¦p¦ó§PÂ_¸ê®Æ¨Ó·½¬O 1 ÁÙ¬O 2 °Ï°ìªº¸ê®Æ?
¦pªG2 ­Ó°Ï°ì³£¦³¸ê®Æ®É­n¥H­þ­Ó¬°¥D ?
samwang µoªí©ó 2021-11-25 12:15

    ¤£·|¨â­Ó°Ï°ì³£¦³¸ê®Æ¡A¥u·|¦³¨ä¤¤¤@­Ó

samwang

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


  1.Ãö©ó&©Î$»P%¡AVBA®Ñ¤W¨S¥X²{¹L³o­Ó¡A¤£¾å±o¬O«ü¤°»ò·N«ä >>¥i¤Wºô§ä¤@¤U´N¦³¡A¨º¨Ç¬O Dim ªºÂ²¼g "&" = as Long
    2.
       Dim MyArray(1 To 10, 5 To 15, 10 To 20)    ' Declare array variables.
       Upper = UBound(MyArray, 1)    ' Returns 10.
       Upper = UBound(MyArray, 3)    ' Returns 20.
       ºô¸ô¤W¥u¬Ý¹L¤W­±³oºØ¥Îªk
      Arr = [n13].CurrentRegion      
       UBound(Arr, 2)>>>>³o¬O«ü¤°»ò·N«ä©O¡H >> ¨úArrªºÄæ¦ì¼Æ
   3.¸ê®Æ¨Ó·½¥Ø«e·|¦³¨âºØ¡A²Ä¤@ºØ¬O¨BÆJ1>¨BÆJ2>¨BÆJ3....³o±z¤W¦¸¦³À°§Ú¸Ñ¨M
      ­Y¬O²Ä¤GºØ¥iª½±µ¶K¤W¨BÆJ2¡A¨Ã¥Bµn°O¨ì¨BÆJ3......¥i¥H§ï¦¨¦pªG¨BÆJ1¬OªÅ®æª½±µ§ì¨BÆJ2¶K¤W¨BÆJ3¶Ü¡H >> 2°Ï³£¦³¸ê®Æ®É­n¥H­þ­Ó¬°¥D??

samwang

¦^´_  samwang


¤£¦n·N«ä¡A§Ú¨S»¡²M·¡¡A
¦]¬°¨Ó·½·|¦³¨âºØ®æ¦¡¡A
¦p¬O¨BÆJ1ªº®æ¦¡¡A¬yµ{´N¬O¨BÆJ1>¨B ...
ÅÚ½³ªd µoªí©ó 2021-11-25 11:58

¦p¦ó§PÂ_¸ê®Æ¨Ó·½¬O 1 ÁÙ¬O 2 °Ï°ìªº¸ê®Æ?
¦pªG2 ­Ó°Ï°ì³£¦³¸ê®Æ®É­n¥H­þ­Ó¬°¥D ?

ÅÚ½³ªd

¦^´_ 21# samwang


¤£¦n·N«ä¡A§Ú¨S»¡²M·¡¡A
¦]¬°¨Ó·½·|¦³¨âºØ®æ¦¡¡A
¦p¬O¨BÆJ1ªº®æ¦¡¡A¬yµ{´N¬O¨BÆJ1>¨BÆJ2>¨BÆJ3
¦p¬O¨BÆJ2ªº®æ¦¡¡A¬yµ{´N¬O¨BÆJ2>¨BÆJ3

ÅÚ½³ªd

¦^´_ 17# ­ã´£³¡ªL


    ±z¦n¡AÁÂÁ±zÀ°§Ú§ïªº¡AÅܪº¶W¯Å¤è«K
    ·Q½Ð±Ð±z´X­Ó¨ç¼Æªº°ÝÃD
    1.Ãö©ó&©Î$»P%¡AVBA®Ñ¤W¨S¥X²{¹L³o­Ó¡A¤£¾å±o¬O«ü¤°»ò·N«ä
    2.
      Dim MyArray(1 To 10, 5 To 15, 10 To 20)    ' Declare array variables.
      Upper = UBound(MyArray, 1)    ' Returns 10.
      Upper = UBound(MyArray, 3)    ' Returns 20.
      ºô¸ô¤W¥u¬Ý¹L¤W­±³oºØ¥Îªk
      Arr = [n13].CurrentRegion      
      UBound(Arr, 2)>>>>³o¬O«ü¤°»ò·N«ä©O¡H
   3.¸ê®Æ¨Ó·½¥Ø«e·|¦³¨âºØ¡A²Ä¤@ºØ¬O¨BÆJ1>¨BÆJ2>¨BÆJ3....³o±z¤W¦¸¦³À°§Ú¸Ñ¨M
      ­Y¬O²Ä¤GºØ¥iª½±µ¶K¤W¨BÆJ2¡A¨Ã¥Bµn°O¨ì¨BÆJ3......¥i¥H§ï¦¨¦pªG¨BÆJ1¬OªÅ®æª½±µ§ì¨BÆJ2¶K¤W¨BÆJ3¶Ü¡H

1. Sub ¸ê®ÆÂà¸m¶K¤W()
   
2. 
   
3. Dim Arr, Brr, i&, j&, T$, R&, C%
   
4. Range("a13:g2000").ClearContents
   
5. 
   
6. If Arr = [n13].CurrentRegion <> "" Then
   
7. ReDim Brr(1 To 2000, 1 To 7)
   
8. For j = 1 To UBound(Arr, 2)
   
9.     For i = 1 To UBound(Arr)
   
10.         T = Arr(i, j): If T = "" Then GoTo i01
    
11.         C = C + 1: Brr(R + 1, C) = T
    
12.         If C = 7 Then C = 0: R = R + 1
    
13. i01: Next i
    
14. Next j
    
15. [a13].Resize(R + 1, 7) = Brr
    
16. Else
    
17. 
    
18. 
    
19. End Sub

½Æ»s¥N½X