ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

MID.VLOOKUPªºVBA«ç»ò¼g

MID.VLOOKUPªºVBA«ç»ò¼g

¸ß¨D¤j¤jÀ°§Ú¸Ñµª
½Ð§k§ÚÀ³¸Ó¦p¦ó¥ÎVBA¼g
¦]¬°¥ÎVLOOKUP¸ê®Æ¶V¦h¶]¤ëºC
MID§Ú¤S¤£ª¾¹DÀ³¥[¦b­þ

¸ß°Ý.rar (16.29 KB)

¦^´_ 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

TOP

owen06¤j
½d¨Ò¥i¥H¥Î.¦ý®M¥Î¨ì§Úªºªí®æ
§ÚÁÙ¬O·d¤£À´
§Ú­«·s§¨§¨±aÀÉ
½Ð¦AÀ°§Ú¸Ñµª
¦]¬°§Ú«e­±¥[¤FF´N¤£¯à°Ê¤F

¸ß°Ý.rar (14.7 KB)

TOP

¦^´_ 3# sheau-lan


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

TOP

¦pªG¤£¥[F³£¥i¥H¥Îªü
¥i¬O¥[¤FF´N¤£¯à¥Î¤F
§Ú¬O¤£¬O­n§ï­þ¸Ì

TOP

¦^´_ 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

TOP

­þ¦pªG­n§ï6½X§ÚÀ³¸Ó«ç»ò°µ©O
§Ú¦³¹Á¸Õ°µ­×§ï¦ý³£¤£¯à°Ê
code = Left(.Value, 3) * 1
¬O³o¤@¬q­n­×§ï¶Ü?¥i§_½Ð¤j¤jÀ°§Ú¸Ñµª
ÁÂÁÂ

TOP

¥»©«³Ì«á¥Ñ owen06 ©ó 2014-12-23 11:20 ½s¿è

¦^´_ 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

TOP

¦nªº...§Ú¦b¸Õ¬Ý¬Ý
ÁÂÁ§A¤F

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD