[µo°Ý] §Ç¸¹¦Û°Ê¸õ¸¹¤Î«O¯d¦rÅé

BV7BW

¦U¦ì ¥ý¶i«e½ú ¦Ñ®v ·s¦~¦n
  ¯¬ ¦U¦ì·s¶i«e½ú ¦Ñ®v
         ·s¦~ ¤ß·Q¨Æ¦¨ ¸U¨Æ¦p·N

½Ð±Ð¥ý¶i«e½ú ¦Ñ®v
¦p¦ó±NE5Äx1§Ç¸¹¿é¥X«á¦Û°Ê+1.¦ý«O¯d"Äx"¦r.¨ÒÄx1«á¸õ¤JÄx2

­ã´£³¡ªL

T=[E5]
V=VAL(MID(T,2))
[A1]=T
[E5]=LEFT(T,1) & V+1

BV7BW

¦^´_ 2# ­ã´£³¡ªL
­ã´£³¡ªL ¦Ñ®v§A¦n ·s¦~¦n


ÁÂÁ¦Ѯv«ü¾É

¥Ø«e¾ã²z¤¤.ÁÙ¦³¨Ç¤£©ú¦a¤è
§Ú¬O¥H¦Ñ®v«ü¾É¥X³f§@·~Dª©V01_10905ª©¤¤­×§ï
­ì¥»(e5)¬OªÅ®æ²{¦b»Ýµ¹¤©Äx¸¹(²{¦b¬O¥Î¤â°Ê¥h½Õ¾ã)
¬O§_¥i¥H¸ò³æ¸¹¼Ò¦¡¤@¼Ë¿é¥X«á¦Û°Ê¸õ¸¹¨ÒÄx1¿é¥X«á¸õ¬°Äx2)
³o¬q§ÚÁÙ¤£©ú¦p¦ó­×§ï
½Ð¦Ñ®v¦A¦¸«ü±Ð ÁÂÁÂ

Andy2483

¦^´_ 3# BV7BW


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
¾ÇÂǦ¹¥DÃD½m²ßIJµoªºª¾ÃÑ,¥H¤U¬O«á¾Çªº«Øij¤èªk,½Ð°Ñ¦Ò

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'¡ô¦b¹w³]ªº«ö¨â¤U°Ê§@¤§«e¡A«ö¨â¤U¤u§@ªí®É¡A§Yµo¥Í¦¹¨Æ¥ó¡C
'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheet.beforedoubleclick
Dim T$, V%
'¡ô«Å§iÅܼÆ:T¬O¦r¦êÅܼÆ,V¬Oµu¾ã¼ÆÅܼÆ
With Target
'¡ô¥H¤U¬OÃö©óIJµoªºµ{§Ç
   If .Address = "$E$5" Then
   '¡ôÂùÀ»·Æ¹«¥ªÁä¦pªGÀx¦s®æ¦ì§}¬O "$E$5" ??
      T = [E5]
      '¡ô¥OT³o¦r¦êÅܼƬO[E5]ªº¤º®e¦r¦ê
      V = Val(Mid(T, 2))
      '¡ô¥OV³o¾ã¼ÆÅܼƬO T¦r¦êÅܼƲÄ2­Ó¦r¶}©l«á­±ªº©Ò¦³¦r¤¸,¦A¥HVal()Âà¤Æ¬°¼Æ­È
      http://forum.twbts.com/viewthrea ... a=pageD1&page=2
      [E5] = Left(T, 1) & V + 1
      '¡ô¥O[E5]Àx¦s®æ­È¬O T¦r¦êÅܼƲÄ1­Ó¦r,³s±µV¾ã¼ÆÅܼÆ+1,©Ò²Õ¦¨ªº·s¦r¦ê
      Cancel = True
      '¡ô¨ú®ø­ìÀx¦s®æ¥ªÁä¹w³]ªº«ö¨â¤U°Ê§@
      Beep
      '¡ôµo¥X¹ÍÁn
   End If
End With
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
'¡ô·í¨Ï¥ÎªÌ©Î¥~³¡³sµ²Åܧó¤u§@ªí¤¤ªºÀx¦s®æ®É·|µo¥Í¦¹¨Æ¥ó¡C
'https://learn.microsoft.com/zh-tw/office/vba/api/excel.worksheet.change
Dim xF As Range, Da&, Db&, xRow&, VL
'¡ô«Å§iÅܼÆ:xF¬OÀx¦s®æÅܼÆ,(Da,Db,xRow)¬Oªø¾ã¼ÆÅܼÆ,VL¬O³q¥Î«¬ÅܼÆ
With Target
'¡ô¥H¤U¬OÃö©óIJµoªºµ{§Ç
     If .Address = "$K$1" Then
     '¡ôÀx¦s®æ­È½s¿è¦pªGÀx¦s®æ¦ì§}¬O "$K$1"  ??
        Set xF = [K2:K6000].Find(.Value, LookIn:=xlValues, lookat:=xlWhole)
        '¡ô¥OxF³oÀx¦s®æÅܼƬO ¥ÎFind()¤èªk,¥H[K1]­È´M§ä[K2:K6000]Àx¦s®æ¸Ì ­È¥þ³¡¬Û¦PªºÀx¦s®æ
        'https://learn.microsoft.com/zh-tw/office/vba/api/excel.range.find
        If Not xF Is Nothing Then xRow = xF.Row
        '¡ô¦pªGxF³oÀx¦s®æÅܼƦ³§ä¨ì,´N¥OxRowÅܼƬO xF³oÀx¦s®æÅܼƪº¦C¸¹¼Æ¦r
        If .Value = "" Then xRow = 3
        '¡ô¦pªG[K1]­È¬O ªÅ¦r¤¸,´N¥OxRowÅܼƬO 3
        If xRow > 0 Then ActiveWindow.ScrollRow = xRow: Range("M" & xRow + 1).Select
        '¡ô¦pªGxRowÅÜ¼Æ > 0,´NÅý xRowÅܼƦC©óµøµ¡¦b¦Ü³»¦ì¸m:¥OÀx¦s®æ´å¼Ð¦bMÄ檺¤U¤@¦C¤W
     End If
     Beep
End With
End Sub

BV7BW

¦^´_ 4# Andy2483
   Andy248 ¥ý¶i¥ý¥Í§A¦n ÁÂÁ§A«ü±Ð
¥Ø«e¸g´ú¸Õ§¹¥þ¦¨¥\¹B§@.¥H¥i¨Ï¥Î
«e´£¶·¨Æ¥ýÂIÀ»E5®æ¤~·|¦Û°Ê¸õ¸¹.
¬O§_¥i·f°t¿é¥X«ö¯ÃÁä.¦Û°Ê¸õ¸¹
¨ÒV2(1111209001).E5À³¬O(Äx1)
¸g¿é¥X«á V2¤w¦Û°Ê¸õ¸¹¬°(1111209002).E5«h¥i¸òV2³Ì«á½X(2)¸õ¬°(Äx2)
¤]´N¬OE5¬O¸òV2³Ì«á½X¦P¨B¸õ°Ê
¦³³Ò¥ý¶i«ü¾É ÁÂÁÂ

Andy2483

¦^´_ 5# BV7BW


    ÁÂÁ«e½ú¦^´_
«á¾Ç«Øij¦p¤U:

«e½ú»Ý¨D: ¨ÒV2(1111209001).E5À³¬O(Äx1),¸g¿é¥X«á V2¤w¦Û°Ê¸õ¸¹¬°(1111209002).E5«h¥i¸òV2³Ì«á½X(2)¸õ¬°(Äx2)

E5¿é¤J¤½¦¡  = "Äx" & INT(RIGHT(V2,2))
1."Äx" ¤å¦r³s±µ  INT()¨ç¼Æ ¦^¶Çªº¼Æ¦r,³s±µ«á´NÅܦ¨·s¤å¦r¦ê
INT ¨ç¼Æ:  ±N¼Æ¦rµL±ø¥ó±Ë¦ì¦Ü³Ì±µªñªº¾ã¼Æ
https://support.microsoft.com/zh-tw/office/int-%E5%87%BD%E6%95%B8-a6c4af9e-356d-4369-ab6a-cb1fd9d343ef

2.±N³QµL±ø¥ó±Ë¦ì¦Ü³Ì±µªñªº¾ã¼Æªº­È¬O RIGHT(V2,2) À³¸Ó²£¥Íªº
RIGHT(V2,2)=RIGHT("1111209001" ,2) = "01"
RIGHT ¨ç¼Æ:RIGHT ·|¶Ç¦^¤@¤å¦r¦r¦êªº³Ì«á¦r¤¸©Î¦r¤¸²Õ (¨Ì¾Ú±z©Ò«ü©wªº¦r¤¸²Õ¼Æ)¡C
https://support.microsoft.com/zh-tw/office/right-rightb-%E5%87%BD%E6%95%B8-240267ee-9afa-4639-a02b-f19e1786cf2f

µ²»y:
1."Äx"¤å¦r ³s±µ §âV2Àx¦s®æ­È¨ú¥kÃä 2­Ó¤å¦r«á,Âà´«¦¨ ¾ã¼Æ ªº·s¦r¦ê
2.§â  "1111209001" ¨ú¥kÃä 2­Ó¤å¦rªºµ²ªG¬O "01" ¦r¦ê,¦A±N "01" ¦r¦ê Âà´«¦¨ ¾ã¼Æ 1
3.§â  "1111209002" ¨ú¥kÃä 2­Ó¤å¦rªºµ²ªG¬O "02" ¦r¦ê,¦A±N "02" ¦r¦ê Âà´«¦¨ ¾ã¼Æ 2
4.§â  "1111209099" ¨ú¥kÃä 2­Ó¤å¦rªºµ²ªG¬O "99" ¦r¦ê,¦A±N "99" ¦r¦ê Âà´«¦¨ ¾ã¼Æ 99
5.§â  "1111209102" ¨ú¥kÃä 2­Ó¤å¦rªºµ²ªG¬O "02" ¦r¦ê,¦A±N "02" ¦r¦ê Âà´«¦¨ ¾ã¼Æ 2,
¦pªG¨ú¥kÃä¨â­Ó¦r´N»P²Ä3¶µ­«½Æ¤F,©Ò¥H»Ý­n¦Ò¼{²M·¡:¨ú2¦r¤¸ ©Î ¨ú3¦r¤¸

BV7BW

¦^´_ 6# Andy2483


    ·PÁÂAndy248 ¥ý¶i ²Ó¤ß«ü¾É
§¹¥þ²z¸Ñ¹B¥Î¤¤
¤×¨ä³o¬q¦pªG¨ú¥kÃä¨â­Ó¦r´N»P²Ä3¶µ­«½Æ¤F,©Ò¥H»Ý­n¦Ò¼{²M·¡:¨ú2¦r¤¸ ©Î ¨ú3¦r¤¸
¨S·Q¨ì«D±`»Ý­n²Ó¤ß¨Æ¥ó

ÁÂÁÂ Andy248 ¥ý¶i