Hsieh

1. Sub getmynumber()
   
2. Dim Ay(), A As Range, Ar As Variant, s&, i%
   
3. For Each A In Range([C2], [C65536].End(xlUp))
   
4.    If Len(A) - Len(Replace(A, "-", "")) = 2 Then
   
5.    Ar = Split(A, "-")
   
6.    For i = Val(Ar(1)) To Val(Ar(2))
   
7.       ReDim Preserve Ay(s)
   
8.       Ay(s) = Ar(0) & "-" & i
   
9.       s = s + 1
   
10.    Next
    
11.    Else
    
12.       ReDim Preserve Ay(s)
    
13.       Ay(s) = A
    
14.       s = s + 1
    
15.    End If
    
16. Next
    
17. [G2:G65536] = ""
    
18. [G2].Resize(s, 1) = Application.Transpose(Ay)
    
19. End Sub

複製代碼

Hsieh

回復 5# 霸氣走全身

1. Sub SSS()
   
2. Dim Ay(), A As Range, Ar As Variant, s&, i%
   
3. For Each A In Range([D2], [D65536].End(xlUp))
   
4.    If Len(A) - Len(Replace(A, "-", "")) = 2 Then
   
5.    Ar = Split(A, "-")
   
6.    For i = Val(Ar(1)) To Val(Ar(2))
   
7.    For j = 1 To A.Offset(, 1)
   
8.    If A.Offset(, 1) > 1 Then temp = Chr(64 + j) Else temp = ""
   
9.       ReDim Preserve Ay(s)
   
10.       Ay(s) = Array(A.Offset(, -2).Value, A.Offset(, -1).Value, Ar(0) & "-" & i & temp)
    
11.       s = s + 1
    
12.    Next
    
13.    Next
    
14.    Else
    
15.    For j = 1 To A.Offset(, 1)
    
16.    If A.Offset(, 1) > 1 Then temp = Chr(64 + j) Else temp = ""
    
17.       ReDim Preserve Ay(s)
    
18.       Ay(s) = Array(A.Offset(, -2).Value, A.Offset(, -1).Value, A.Value & temp)
    
19.       s = s + 1
    
20.    Next
    
21.    End If
    
22. Next
    
23. [K2:M65536] = ""
    
24. [K2].Resize(s, 3) = Application.Transpose(Application.Transpose(Ay))
    
25. End Sub

複製代碼