Blade

Selection.Resize(, 13).Copy Sheets("invoice").Selection.Resize(, 13)
Selection.Resize(, 13).Copy Sheets("invoice").Selection.Resize(, 1).Paste
Selection.Resize(, 13).Copy Sheets("invoice").Range("B13").Select

Blade

§Úªº¬O2010

Blade

·í§Ú¿ï¤F¡A«OÅ@¤u§@ªí¡A¥H¤Uµ{¦¡«K·|¥X¿ù»~¡C

1. Sub InvoiceNo()
   
2.     Dim xRNo As Range, i As Integer, y As Integer, R As Integer, RR As Integer
   
3.     Set xRNo = Range("Q2")
   
4.     'Set xRNo = Range("D5")
   
5.     y = Len(xRNo)                                      '[µo²¼½s¸¹]ªº¦r¦ê­Ó¼Æ
   
6.     For i = 1 To y
   
7.         If R = 0 And Mid(xRNo, i, 1) Like "[0-9]" Then R = i    '§ä[µo²¼½s¸¹]¤¤²Ä¤@­Ó¼Æ¦r
   
8.         If Mid(xRNo, i, 1) Like "[!0-9]" Then RR = i            '§ä[µo²¼½s¸¹]¤¤³Ì«áªº¤å¦r
   
9.     Next
   
10.     If RR > R Or R = 0 Or xRNo = 0 Then  '¼Æ¦r¦b¤å¦r¤§«e(©Î¥u¦³¤å¦r),¥u¦³¼Æ¦r
    
11.         MsgBox "¡iª`·N¡j¦¬¾Ú½s¸¹¥X¿ù !!!"
    
12.    Else
    
13.         xRNo = Mid(xRNo, 1, R - 1) & Format(Mid(xRNo, R) + 1, String((y - R + 1), "0"))
    
14.     End If
    
15.    
    
16.     Range("Q2").Select
    
17.   'Range("D5").Select
    
18.     Selection.Copy
    
19.     Range("D6").Select
    
20.     ActiveSheet.Paste
    
21.     Application.CutCopyMode = False
    
22.     With Selection.Font
    
23.         .ColorIndex = xlAutomatic
    
24.         .TintAndShade = 0
    
25.     End With
    
26.     Range("A4").Select
    
27.      '¦p  y - R + 1 = 5
    
28.      '¦p :Format(568, String((y - R + 1), "0")) => Format(568, "00000") => 5¦ì¼Æ:  00568
    
29. End Sub

½Æ»s¥N½X

¥X¿ù»~ªº¥y¤l

1.         xRNo = Mid(xRNo, 1, R - 1) & Format(Mid(xRNo, R) + 1, String((y - R + 1), "0"))

½Æ»s¥N½X