序號太常無法累加如圖

show5217

下載 (106.64 KB)
求解

2019-2-20 10:08

Hsieh

回復 1# show5217

1. Private Sub CommandButton1_Click()
   
2. ReDim ar(1 To TextBox1)
   
3. mystr = Split([A1], "-")(0) & "-"
   
4. For i = 1 To TextBox1.Value
   
5.   ar(i) = mystr & Replace([A1], mystr, "") + i - 1
   
6. Next
   
7. [A1].Resize(TextBox1, 1) = Application.Transpose(ar)
   
8. End Sub
   
9. 
   
10. Private Sub CommandButton2_Click()
    
11. Range([A2], [A2].End(xlDown)).ClearContents
    
12. End Sub

複製代碼

show5217

回復 2# Hsieh


   誤會我的意思了  謝謝 已用這個方法解決
Private Sub CommandButton1_Click()
If TextBox1.Value = "" Or Cells(1, 1) = "" Then Exit Sub
If IsNumeric(Cells(1, 1)) Then
For i = 2 To TextBox1.Value
    Cells(i, 1) = Cells(i - 1, 1) + 1
Next
Else
Set reg = CreateObject("vbscript.regexp")
With reg
    .Global = True
    .Pattern = "[0-9]"
For Each Match In .Execute(Cells(1, 1))
    numb = numb & Match
Next
End With
eng = Left(Cells(1, 1), Len(Cells(1, 1)) - Len(numb))
For i = 2 To TextBox1.Value
    Cells(i, 1) = eng & (numb + i - 1)
Next
'[a1].AutoFill Destination:=Range("a1:a" & TextBox1.Value + 1), Type:=xlfillserise
End If
End Sub

Private Sub CommandButton2_Click()
Columns(1).ClearContents
End Sub