[µo°Ý] ¤ÀÂ÷³æ¸¹

PJChen

¤j¤j¦n,
EÄ檺³æ¸¹,§t¦³2­Ó"-",§@¬°¤ÀÂ÷°ò·Ç,³æ¸¹¤ÀÂ÷¦ÜR¡BS¡BT¤TÄ椤
TÄæ³æ¸¹: 3½X+"-" (¤£¨¬3½X,«e­±¸É0)
SÄæ³æ¸¹: 4½X+"-" (¤£¨¬4½X,«e­±¸É0)
RÄæ³æ¸¹: 3½X (¤£¨¬3½X,«e­±¸É0)

ex:
±ÄÁʳ渹½X        ¥½½X        ¤¤¶¡½X        ³æ¸¹
886-1215-283        283        1215-        886-
872-1305-017        017        1305-        872-
010-0105-547        547        0105-        010-

½Ð°Ý­n¦p¦ó¼g³o­Óµ{¦¡?   ¤ÀÂ÷³æ¸¹_Split.rar (151.35 KB)

¤U¸ü¦¸¼Æ: 13

2021-7-4 23:10

1. Sub ¤ÀÂ÷³æ¸¹()
   
2. Dim xB As Workbook, R&, xD, Arr2, Arr3, Arr4
   
3. R = Cells(Rows.Count, "E").End(xlUp).Row
   
4. If R <= 2 Then Exit Sub
   
5. Set xB = Workbooks("¤ÀÂ÷³æ¸¹_Split.xlsx")
   
6. With xB.Sheets("¥_°Ï")
   
7.      R = .Cells(Rows.Count, 1).End(xlUp).Row - 1
   
8.     If R > 0 Then arr = Split([E2], "-")
   
9.      If R > 0 Then Arr2 = Split([E2], "-").Resize(R).Value
   
10.      If R > 0 Then Arr3 = Split([E2], "-").Resize(R).Value
    
11.      If R > 0 Then Arr4 = Split([E2], "-").Resize(R).Value
    
12. End With
    
13.     xB.Sheets("¥_°Ï").[r2].Resize(R).Value = Arr2
    
14.     xB.Sheets("¥_°Ï").[s2].Resize(R).Value = Arr3
    
15.     xB.Sheets("¥_°Ï").[t2].Resize(R).Value = Arr4
    
16. Next
    
17. End Sub

½Æ»s¥N½X

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú«ü¾É
«á¾ÇÂǦ¹©«¾Ç¨ì¶¶°j°é¸Ì°fµÛ¶]µ²ªG,³o¬O«á¾Ç³o±e¤~±q¨S·Q¹Lªº,¾Ç²ß¤ß±o¦p¤U,½Ð«e½ú¦A«ü¾É
°õ¦æ«e:

¤U¸ü (171.73 KB)

2023-11-30 10:45

°õ¦æµ²ªG:

¤U¸ü (188.71 KB)

2023-11-30 10:45

Option Explicit
Sub TEST_A1()
Dim Arr, i&, j%, Vr, TR
'¡ô«Å§iÅܼÆ
Arr = Range([g1], [e65536].End(3))
'¡ô¥OArrÅܼƬO¤Gºû°}¦C,¥HÀx¦s®æ­È±a¤J
Vr = Array("000-", "0000-", "000")
'¡ô¥OVrÅܼƬO¤@ºû°}¦C
For i = 3 To UBound(Arr)
'¡ô³]¶¶°j°éi
    TR = Split(Arr(i, 1) & "--", "-")
    '¡ô¥OTRÅܼƬO¤@ºû°}¦C: Arr°}¦C­È³s±µ"--"¦¨¬°·s¦r¦ê«á,¦A¥H"-"¤À³Î¸Ó¦r¦ê¦¨
    '(¯Á¤Þ¸¹¦Ü¤Ö·|¦³0~2)
    For j = 0 To 2
    '¡ô³]¶¶°j°éj
        Arr(i - 2, 3 - j) = IIf(IsNumeric(TR(j)), Format(TR(j), Vr(j)), "")
       '¡ô¥O­ì¨ÓªºArr°}¦C­È±q²Ä1¦C¶}©l¼g¤J·s°}¦C­È
        'IIf():¦pªGIsNumeric(TR(j))ÅÞ¿è­È¬OTrue,´N¦^¶Ç Format(TR(j), Vr(j))¦r¦ê,
        '§_«h¦^¶ÇªÅ¦r¤¸
        'IsNumeric(TR(j)):TR°}¦Cªºj°j°é¼Æ¯Á¤Þ¸¹°}¦C­È¬O¤£¬O¼Æ­È?? ¦^¶ÇTrue ©Î False
        'Format(TR(j), Vr(j)):¥OTR°}¦Cªºj°j°é¼Æ¯Á¤Þ¸¹°}¦C­ÈÂà¤Æ¦¨
        'Vr°}¦Cªºj°j°é¼Æ¯Á¤Þ¸¹°}¦C­È³W«hªº®æ¦¡¦r¦ê
    Next j
Next i
With [r3].Resize(UBound(Arr) - 2, 3)
'¡ô¥H¤U¬OÃö©ó[R3]Àx¦s®æÂX®i©Ò»Ý½d³òÀx¦s®æªºµ{§Ç
     .NumberFormatLocal = "@"
     '¡ô¥O¸Ó½d³òÀx¦s®æ®æ¦¡¬O ¤å¦r
     .Value = Arr
     '¡ô¥O¸Ó½d³òÀx¦s®æ­È¥HArr°}¦C­È±a¤J
End With
End Sub

singo1232001

¦^´_ 10# PJChen


Ãö©ó©â¶Hªº°ÝÃD ¸Ñ¨M¤è¦¡¦p¤U
¥ý½m²ß¦p¦ó¾Þ§@"·s¼WºÊ¬Ý¦¡"
https://www.youtube.com/watch?v=SvUFa37qOXo
7:08’
©Î
https://blog.gtwang.org/programming/excel-vba-debug-error-handling/

µM«á ¶}excel vba
¿é¤J
Sub aa()
Dim ar, ar1
ar = Array(1, 2, 3)
ar1 = Array(1, 2, 3)
ReDim Preserve ar(1 To 3)
ReDim Preserve ar1(2 To 4)

Debug.Print ar(1)
Debug.Print ar1(1) '<--¦¹¦æ·|¥X¿ù¥¿±` ½ÐÄ~Äò¬Ý¤U¥h
End Sub

1.ª`·NºÊ¬Ý¦¡ ar ,ar1ªº®t§O ½Ð±N¥ª«e¤èªº " ¥Ð " ¦r²Å¸¹ÂI¶} ¨Ã§PÂ_¨âªÌªÅ¶¡½s¸¹ªº¤£¦P
2.¬O§_ar1(1) ¥X²{°}¦C¶W¹L½d³ò   ¥Nªí¨S¦³ar1(1)ªº¦ì¸m
­×§ï¬°ar1(2) ¬O§_¥¿±`

¥t¥~¥i¥H¸ÕµÛ¥[¤J
ar2=array(1,2,3)
redim Preserve ar2( 3)
·|µo²{¸ò redim Preserve ar( 1 to 3) ¤£¤@¼Ë
¦]¬°
redim Preserve ar2( 3)
¬OÁY¼g
­ì©l¥¿½Tªº¼gªk¬O
redim Preserve ar2(0 to 3)

µ²½×:¸g¹L¤j¶qªº¨Ï¥Î¸gÅç ¤ÏõX«Øij­è±µÄ²ªÌªÌºÉ¶q¦h¹Á¸Õ 0 to nªº¼gªk,
¥Ñ©ó°}¦Cªº °Ï°ì³]¸m¤j¤p(n to n)¥Îªk «Ü®e©ö»PÀx¦s®æÄæ¦C¦ì¸¹½X·f°t °j°é·f°t
«áÄò¥i¥H´î¤Ö³\¦h·L½Õªº¨BÆJ   ¤]·|¸û¬°¤u¾ãª½Æ[

­YÁÙ¬O¦³ºÃ´b google ÃöÁä¦r"redim 1 to n ¥Îªk"

PJChen

¦^´_ 9# n7822123

°õ¦æ«á,ı±o«Ü©â¶H@@

n7822123

¦^´_ 5# PJChen

©w¸qBrr¬O­Ó2ºû°}¦C¡A¥¦ªº1ºû¤W¬Éµ¥©óArr°}¦Cªº1¬°¤W¬É(Rows)¡A2ºû¬É­­¬O1~3(Columns)

¥H¤U³o­Ó½d¨Òµ{¦¡¥i¥HÀ°§A¤F¸Ñ °}¦Cªº«Å§i¡A¥H¤Î°}¦Cªº¬É­­°Ï¶¡

ª½±µ°õ¦æ´N¥i¥H¤F¡A¬Ý°T®§¨Ã¹ï¤ñµ{¦¡

Sub Ex()
Dim Arr(1 To 10)                  '©w¸q¤@­Ó¤@ºû°}¦CArr
Str1 = "Arrªº¬É­­=" & LBound(Arr) & "~" & UBound(Arr) & vbCrLf & vbCrLf
Dim Brr(2 To 20, 3 To 30)   '©w¸q¤@­Ó¤Gºû°}¦CBrr
Str2 = "Brrªº1ºû¬É­­=" & LBound(Brr, 1) & "~" & UBound(Brr, 1) & vbCrLf
Str3 = "Brrªº2ºû¬É­­=" & LBound(Brr, 2) & "~" & UBound(Brr, 2) & vbCrLf
MsgBox Str1 & Str2 & Str3
End Sub

PJChen

¦^´_ 7# ­ã´£³¡ªL
¦n¤[¤£¨£....­ã¤j¦n,
µ{¦¡¹B§@OK

¯à§_¸Ñ¬õ¦r?
Arr = Range([g1], [e65536].End(3))
¥tµ{¦¡¤¤¨S¦³R:T, ¬°¦ó¥¦¯à¦Û°Ê¸m¤J©O?

­ã´£³¡ªL

­Y³£¬O¯Â¼Æ¦r~~
Sub TEST_A1()
Dim Arr, i&, j%, Vr, TR
Arr = Range([g1], [e65536].End(3))
Vr = Array("000-", "0000-", "000")
For i = 3 To UBound(Arr)
    TR = Split(Arr(i, 1) & "--", "-")
    For j = 0 To 2
        Arr(i - 2, 3 - j) = IIf(IsNumeric(TR(j)), Format(TR(j), Vr(j)), "")
    Next j
Next i
With [r3].Resize(UBound(Arr) - 2, 3)
     .NumberFormatLocal = "@"
     .Value = Arr
End With
End Sub

'================================

­ã´£³¡ªL

¦^´_ 5# PJChen


ubound(arr) ... 緃¦V¦æ¼Æ...¬Û·í¤_«ü©w½d³òªºrows.count
ubound(arr,2) ... ¾î¦VÄæ¼Æ...¬Û·í¤_«ü©w½d³òªºcolumns.count

for i = 2 to ~~
arrªº½d³ò¥]§t²Ä¤@¦æ¼ÐÃD, ²Ä¤@¦æ¤£¶·³B²z, ¬G±q2°_©l~~

PJChen

¦^´_ 3# n7822123
¦^´_ 2# singo1232001
¦^´_ 4# samwang

ÁÂÁ¤T¦ì, µ²ªG³£«Ü§¹¬ü¡I
½Ð°ÝN¤j (n7822123)
¯à§_À°¦£¸Ñ»¡¥H¤Uµ{¦¡¬õ¦r? §Ú¹ïUBoundªº¼Æ¦r¹ïÀ³«Ü¤£²z¸Ñ@@
.NumberFormatLocal = "@"
ReDim Brr(1 To UBound(Arr), 1 To 3)
For R = 2 To UBound(Arr)

samwang

¦^´_ 1# PJChen

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, Brr(), a, w$, i&, j%
Columns("R:T").NumberFormatLocal = "@"
Arr = Range([E2], [E2].End(4))
ReDim Brr(1 To UBound(Arr), 1 To 3)
For i = 2 To UBound(Arr)
    a = Split(Arr(i, 1), "-")
    If UBound(a) < 2 Then GoTo 99
    For j = 0 To UBound(a)
        w = Split(Arr(i, 1), "-")(j)
        If j = 0 Then
            If Len(w) < 3 Then Brr(i, 3) = "0" & w & "-" Else Brr(i, 3) = w & "-"
        ElseIf j = 1 Then
            If Len(w) < 4 Then Brr(i, 2) = "0" & w & "-" Else Brr(i, 2) = w & "-"
        ElseIf j = 2 Then
            If Len(w) < 3 Then Brr(i, 1) = "0" & w Else Brr(i, 1) = w
        End If
    Next
99: Next
[R2].Resize(UBound(Brr), 3) = Brr
End Sub