owen06

¦^´_ 1# sheau-lan


    ¸Õ¸Õ¬Ý©O

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. On Error GoTo 99
   
3. With Target
   
4.    If .Row >= 3 And .Column = 2 And .Offset(-1, 0) <> "" Then
   
5.             If .Value = "" Then
   
6.               .Offset(0, -1) = ""
   
7.               .Offset(0, 1) = ""
   
8.             End If
   
9.       code = Left(.Value, 3) * 1
   
10.       num = .Offset(-1, -1)
    
11.       If .Address = [b3].Address Then
    
12.          .Offset(0, -1) = "0001"
    
13.          .Offset(0, 1) = Application.VLookup(code, Sheet2.[a1:b65536], 2, False)
    
14.       Else:
    
15.          .Offset(0, -1) = Application.Text(num + 1, "0000")
    
16.          .Offset(0, 1) = Application.VLookup(code, Sheet2.[a1:b65536], 2, False)
    
17.       End If
    
18.    End If
    
19. End With
    
20. 99
    
21. End Sub

½Æ»s¥N½X

owen06

¦^´_ 3# sheau-lan


    §A½T©w¬O§ì«e¤T½X¶Ü¡H¨º³o¼Ë¤@°ïF20ªº¡A¨t²Î«ç»ò¥i¯à¤ñªº¥X¨Ó¡H

owen06

¦^´_ 5# sheau-lan


    §Úªº·N«ä¬O¡A¤£½×¦³¨S¦³F¡A§A¦pªG¥u§ì«e¤T½X¡A·|Åܦ¨F20¡A¨º¦b«È¤á¥N¸¹¨ºÃä¤ñ¹ïªº®É­Ô¡A´N·|¦³¤T­Ó¸ê®Æ³£¬OF20¶}ÀYªº¤F¡A
    ³o¼Ë¨t²Î·|§ì¤£¥X¥¿½T¸ê®Æ¡A¦ó¤£°®¯Ü§ì6½X¡A¤~¯à§¹¾ãªº¸ò«È¤á¥N¸¹¨ºÃ䪺¸ê®Æ§¹¥þ²Å¦X¡C

owen06

¦^´_ 7# sheau-lan

    ¸É¥R¤@¤U¡A§A¥ÎLEFT¡BMID¡BRIGHT¥h§ì¥X¯S©w¦r¦ê«á¡AÁöµM§A¬Ý¨ìªº¼Ë»ª¬O¼Æ¦r¡A¦ý¥Lªº¥»½è¨ä¹ê¬O¤å¦r¡A³o¼Ë§Avlookup¬O¤ñ¤£¥X¨Óªº¡A©Ò¥H§AÁÙ­n¦A§â¥L*1¡A¥Lªº ¥»½è¤~·|§ïÅܬ°¼Æ­È¡A¦Ó§AªºÀɮ׫e­±¦³­ÓF¡A¥L´N¬O¤å¦r®æ¦¡¡A¦]¦¹¤£»Ý­n¦A¥Î*1¡C
    §ï³o¼Ë§A¸Õ¬Ý¬Ý

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. On Error GoTo 99
   
3. With Target
   
4.     code = Left(.Value, 6)
   
5.     num = .Offset(-1, -1)
   
6.       If .Row >= 3 And .Column = 2 And .Offset(-1, 0) <> "" Then
   
7.             If .Value = "" Then
   
8.               .Offset(0, -1) = ""
   
9.               .Offset(0, 5) = ""
   
10.             ElseIf .Address = [b3].Address Then
    
11.               .Offset(0, -1) = "0001"
    
12.               .Offset(0, 5) = Application.VLookup(code, Sheet2.[a1:b65536], 2, False)
    
13.             Else:
    
14.               .Offset(0, -1) = Application.Text(num + 1, "0000")
    
15.               .Offset(0, 5) = Application.VLookup(code, Sheet2.[a1:b65536], 2, False)
    
16.             End If
    
17.      End If
    
18. End With
    
19. 99
    
20. End Sub

½Æ»s¥N½X