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

¥X³f³æ«Ø¥ß

¦^´_ 9# cclo0728
«e½ú«Ü¦³·Qªk
«á¾Ç½Ð±Ð«e½ú
1.¥X³f¸ê®Æªí¬O¥Î¨Ó¦C¦L¡H¦C¦L§¹´N§R°£
2.ÁÙ¬O²Ö¿n¨ì¹j¤Ñ¤~§R°£¡H
3.©ÎªÌ¬O·í¤Ñ¦h¦¸²Ö¿n«á¦C¦L¡H
4.ÁÙ¬O¨ä¥¦±¡¹Ò¡H

5.¸ê®Æ¿é¤J·|¤@ª½²Ö¿n¸ê®Æ¡H
6.ÁÙ¬O¸ê®Æ¿é¤J¶×¦Ü¥X³f¸ê®Æ«á´N§R°£¡H
7.ÁÙ¬O²Ö¿n¸ê®Æµ¹¨ä¥L¤H­û¬Ý¦Ó¤w

¦]¬°³o¨ÇºÃ°Ý·|¼vÅT¦¹¼Óªº±¡¹Ò
¨Ò¦p¡G
¸ê®Æ¿é¤Jªí¥u¬O¯Âºé¿é¤J¤¶­±ªí¡HÁÙ¬O¸ê®Æ®w¡H

TOP

¦^´_ 11# Andy2483
·PÁ­@¤ß¬Ý§¹
5.¸ê®Æ¿é¤J·|¤@ª½²Ö¿n¸ê®Æ¡H
7.ÁÙ¬O²Ö¿n¸ê®Æµ¹¨ä¥L¤H­û¬Ý¦Ó¤w
¦]¿Ë¤H·Q¦b¥X³f³o¶ô«Ø¥ß¸ê®Æ¡A¥H§Q«áÄò¬d¸ß·í®É³ø»ù»P¬ÛÃö¸ê°T
©Ò¥H·|«Ø¥ß¥X³f©ú²Ó¡A¥H§Q«áÄò¬ÛÃö¨ä¥L­û¤uÆ[¬Ý
©Ò¥H¡A·|·Q­n«Ø¥ß¬y¤ô¸¹¦ê¬d·í¤Ñ¥X³f¸ê°T

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-8 16:13 ½s¿è

¦^´_ 12# cclo0728


    ÁÂÁ«e½ú¦A¦^ÂÐ
½d¨Ò½Ð¸Õ¸Õ¬Ý
[B2].·Æ¹«§Ö«ö¨â¤U¶}©l!´N·|¦³´£¥Ü
¤µ¤Ñ²ß±o¸ê®Æ®w«Ø¥ß
½m²ß°}¦C»P¦r¨å
test_1_20221008_3.zip (38.02 KB)

¸ê®Æ¿é¤J:


¥X³f¸ê®Æ:


¸ê®Æ®w:

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-9 11:35 ½s¿è

¦^´_ 12# cclo0728
«Øij«e½ú¤½¥Îªº¸ê®Æ®w¿W¥ß¥X¨Ó
1.¥i¥H³]­p­ÓexcelÀÉ°µ¬°¬É­±¡A¦U¨Ì¦Uªº¨Ï¥ÎªÌ¨­¥÷¤Î±K½X«ö¶sµ{§Ç¶}°_¸ê®Æ¿é¤JÀɩΤ£¥²±K½X¥u°ßŪ¶}±Ò¸ê®Æ®w¨Ó¨Ï¥Î
1.1.¶}±Ò«á¬É­±§YÃö³¬
1.2.¬É­±ÀÉ¥i¥H¦h¤H°ßŪ¶}±Ò¥B¤£¯à¥t¦s¡A§Y¨Ï§â¬É­±ÀÉ­n½Æ»s¨ì¨ä¥¦¦a¤è¤]¤£¯à¨Ï¥Î¡A¤è«K©óºÞ²z¡A¥Bµ{¦¡¥i¥HÃä¤W½u¨Ï¥ÎÃä­×§ïµ{¦¡½X
1.3.¸ê®Æ¿é¤JÀÉ»P¸ê®Æ®wÀɬO¿W¥ßªº¡A¤]³£Åý¨Ï¥ÎªÌ«ö¬É­±ÀÉ¡A«ö«ö¶s°ßŪ¶}±Ò¡A¥B³£¨ã³Æ¨Ï¥ÎªÌ¨Ï¥Î¤¤®É¡Aµ{¦¡¶}µo­û­×§ïµ{¦¡½Xªº®ÄªG¡A¤è«K§ó·sµ{¦¡ª©¥»
1.4.¥i¥H°O¿ý½Ö¨Ï¥Î¤FÀɮצs¤J·sªº¥X³f¸ê®Æ
........

Excel vb«Ü¦n¥Î¡I«e½ú«Ü¦³·Qªk¡I
«á¾Ç¸gÅç¤À¨É¡A¶È¨Ñ«e½ú°Ñ¦Ò¡I

TOP

¦^´_ 14# Andy2483

·PÁÂÀ°¦£¡AÅý§Ú§l¦¬¤@¤U¡A¦³¨Ç­n«ä¦Ò¤@¤U§A¼gªºÅÞ¿è
ÁÂÁ¡A¬Ý¤£À´¦a¤è¦bµo°Ý

TOP

¦^´_ 13# Andy2483


    ÁÂÁ«e½ú¦A¦^ÂÐ
«á¾Ç§â ·j´M¸ê®Æ®w¨M©w³æ¸¹½Æ²ß¨Ãµù¸Ñ¤F¤@¤U!
«á¾Ç¤]¬OÂǥѳo¤@¥DÃD¾Ç¨ìÅܼƪº«Å§i±N¦r¦ê»P¼Æ¦r°µÅÜ´«
ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
¥H¤U¤ß±o¨Ñ«e½ú°Ñ¦Ò

Sub ·j´M¸ê®Æ®w¨M©w³æ¸¹()

'¡õ¼Æ¦r»P¦r¦ê¥i¥H¥Ñ¤@¶}©lªº«Å§i´N¨M©w³oÅܼƬO¦r¦ê©Î¼Æ¦r!
Dim Arr, T, xA, xB, xC, N, Mx&, D1$, O1$, D2&, O2&, M1$, M2&
'¡ô«Å§iÅܼÆ

Set xA = Sheets("¸ê®Æ¿é¤J")
'¡ô¥OxA¬O "¸ê®Æ¿é¤J" ¤u§@ªí

Set xB = Sheets("¥X³f¸ê®Æ")
'¡ô¥OxB¬O "¥X³f¸ê®Æ" ¤u§@ªí

Set xC = Sheets("¸ê®Æ®w")
'¡ô¥OxA¬O "¸ê®Æ®w" ¤u§@ªí

If xB.Cells(Rows.Count, "B").End(3).Row > 1 Then
'¡ô¦pªG "¥X³f¸ê®Æ" ³Ì«á¤@¦C¦C¼Æ¤j©ó1 !

   xB.Activate
   '¡ôµe­±´N¸õ¨ì "¥X³f¸ê®Æ" ¤u§@ªí
   
   MsgBox "ªº¥X³f¸ê®Æ¥¼³B²z!"
   Exit Sub
   '¡ô¥X²{´£¥Ü!µ²§ôµ{¦¡!
   
End If
O1 = xA.[B2]: O2 = O1
'¡ô¥OO1³oÅܼƬO "¸ê®Æ¿é¤J" ¤u§@ªí.[B2] ¦r¦ê
'¡ô¥OO2³oÅܼƬO O1ÅܼÆÂà¾ã¼Æ


M1 = xC.Cells(Rows.Count, "B").End(3)
'¡ô¥OM1³oÅܼƬO "¸ê®Æ®w" ¤u§@ªí.BÄæ³Ì«á¨ºÀx¦s®æ ¦r¦ê

M2 = M1
'¡ô¥OM2³oÅܼƬO M1ÅܼÆÂà¾ã¼Æ

D1 = Format(Date, "yymmdd")
'¡ô¥OD1¬O¤µ¤Ñ¤é´Á²Õ¦¨ªº¦r¦ê¨Ò¦p22/10/11 Âন "221011"

D2 = D1 & "001"
'¡ô¥OD2¬O ²Õ¦¨¤µ¤é²Ä¤@µ§¥X³f³æªº§Ç¸¹(¼Æ¦r)

If InStr(M1, D1) Then
'¡ô¦pªGM1¦r¦ê¦³¥]§tD1¦r¦ê

   xA.[B2] = M2 + 1
   '¡ô±ø¥ó­Y¦¨¥ß!¥Nªí "¸ê®Æ®w" ¤u§@ªí¤w¸g¦³¤µ¤Ñªº§Ç¸¹
   '¡ôM2¬O¼Æ¦r´N+1 ¶ñ¨ì "¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2]

   
   xA.[B2].Interior.ColorIndex = xlNone
   '¡ô¥O "¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2]©³¦âµL¦â
   
   xA.[B2].Font.ColorIndex = 1
   '¡ô¥O "¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2]¦r¦â¬O¶Â¦â
   
   Else
      xA.[B2] = D2
      '¡ô§_«h¥Nªí¤µ¤ÑÁÙ¨S¦³¦s¤J¤µ¤Ñªº¥X³f³æ
      '¡ô"¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2] ´NÅý¥L¬O¤µ¤Ñªº²Ä¤@µ§§Ç¸¹D2

      
      xA.[B2].Interior.ColorIndex = xlNone
      '¡ô¥O "¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2]©³¦âµL¦â
      
      xA.[B2].Font.ColorIndex = 1
      '¡ô¥O "¸ê®Æ¿é¤J" ¤u§@ªíªº.[B2]¦r¦â¬O¶Â¦â
      
End If
End Sub

TOP

¦^´_ 15# cclo0728


    «á¾Çªº½d¨ÒÅÞ¿è¦p¤U
1.·Q­n¦Û°Ê§PÂ_[B2]§Ç¸¹ªº¬y¤ô¸¹!¥²¶·¥ý±N¥X³f¸ê®Æªí¸ê®Æ¦s¨ì¸ê®Æ®w¨Ã²MªÅ¥X³f¸ê®Æªí¸ê®Æ
2.§PŪ¸ê®Æ®w³Ì«á¤@µ§ªº§Ç¸¹¤~µ¹¤©[B2]·sªº§Ç¸¹
3.¦pªG«ö§R°£¥X³f¸ê®Æ_±a¤J·s¸ê®Æ¶s!¤£ºÞ¥X³f¸ê®Æªí¬O¤£¬O¤w²MªÅ!³£·|¸ß°Ý¬O¤£¬O¯uªº­n§R°£Âªº¥X³f¸ê®Æ
4.¥X³f¸ê®Æªí¥i¥H¦A°µ½s¿è«á¤~³s¦P®æ¦¡½Æ»s¨ì¸ê®Æ®w!¥X³f¸ê®Æªí¥i¥H°µ³o³¡¤Àªº½w½Ä!
5.¸ê®Æ¦s¤J¸ê®Æ®w«á­n°O±o¦sÀÉ!·íµM«e½ú¤]¥i¥HÅý¸ê®Æ²¾¨ì¸ê®Æ®w´N¦sÀÉ!

¥H¤U¬O ¦s¤J¸ê®Æ®w µ{¦¡½Xªº¤ß±o¨Ñ°Ñ¦Ò!

Sub ¦s¤J¸ê®Æ®w()
Dim Arr, T, xB, xC
Dim CH
'¡ô«Å§iÅܼÆ

Set xB = Sheets("¥X³f¸ê®Æ").[A1].CurrentRegion.Offset(1, 0).EntireRow
'¡ô¥OxB¬O "¥X³f¸ê®Æ" ¤u§@ªí¦³¨Ï¥Îªº¦C½d³ò©¹¤U°¾²¾1¦Cªº½d³ò
'´N¬O¤£¥]§t²Ä¤@¦Cªº·N«ä


Set xC = Sheets("¸ê®Æ®w")
'¡ô¥OxC¬O "¸ê®Æ®w" ¤u§@ªí

T = xC.Cells(Rows.Count, "B").End(3).Row + 1
'¡ô¥OT¬O "¸ê®Æ®w" ¤u§@ªíªº²Ä1 ­ÓªÅ¥Õ¦C

xB.Copy xC.Cells(T, "A")
'¡ô±N"¥X³f¸ê®Æ" ¤u§@ªíªº¸ê®Æ½Æ»s¨ì"¸ê®Æ®w" ¤u§@ªíªº²Ä1 ­ÓªÅ¥Õ¦C

xB.Delete xlUp
'¡ô±N"¥X³f¸ê®Æ" ¤u§@ªíªº¸ê®Æ§R°£

MsgBox "°O±o­n¦sÀÉ!"
End Sub

TOP

¦^´_ 16# Andy2483
§Ú³oÃ䪺¬yµ{·Qªk¡A¬O·Q­n¸ê®Æ¿é¤J§¹¦¨«á¡A½Æ»s¨ì¥X³f¸ê®Æ¡A³o®É­Ô´N¦sÀɩΦC¦L¥X³f³æ

½Ð°Ý§Ú·Q­nª½±µ¥X³f¸ê®Æ´N·í¸ê®Æ®w©O?
¦A¿é¤J«È¤á®É¡A«ö¶s²£¥Í¤U¤@µ§¬y¤ô¸¹(©ÎªÌ»¡¸ê®Æ¿é¤J§¹¦¨«á¡A½Æ»s¨ì¥X³f¸ê®Æ®É¡A¸g¥Ñµ{¦¡µ¹¤©¬y¤ô¸¹«Ø¥ß¦Ü¥X³f¸ê®Æ¤º)
¹Á¸ÕµÛ­×§ïµ{¦¡¡A¦ý¦n¹³·|¥d¦b¬y¤ô¸¹§P©w³oÃä

TOP

¦^´_ 18# cclo0728
ÁÂÁ«e½ú¦^ÂÐ
«á¾Ç²{¦b¤£¤è«K³B²z¡I
¦pªG¬O¥u¦³·s§Ç¸¹°ÝÃD,«e½ú¥i¦A¸ÕµÛ§ï¬Ý¬Ý¡I«á¾Çªº½d¨Ò¦³µù¸Ñ¡I
Samwangªº½d¨Ò¸ò±zªº»Ý¨D§ó¶Kªñ¡A
¦pªG±z«æµÛ­n¥Î¡A¥i¥H¥H¦^ÂЪº¤è¦¡¡A½Ðsamwang«e½úÀ°¦£!

«á¾Ç¤zÂZ¤F¨â¦ì«e½úªº¬ã°Q¡I
¦V¨â¦ì¹Dºp¡Isorry!

TOP

¦^´_ 9# cclo0728
³o½d¨Ò¦³´X­ÓºÃ°Ý
1.¤@®a¼t°Ó¤@­ÓÀɮסHÁÙ¬O©Ò¦³ªº¼t°Ó²V¦X¦b¤@°_¦b¤@­ÓÀɮ׸̡H
2.§Ç¸¹¬O¤À¦U¼t°Ó²Ö¥[¡HÁÙ¬O©Ò¦³ªº¼t°Ó²V¦X¦b¤@°_¥ý¥X¥ý²Ö¿n§Ç¸¹¡H

³Ì¦n¬O´£¨Ñ§ó¶Kªñ»Ý¨D±¡¹Òªº½d¨Ò¡I

TOP

        ÀR«ä¦Û¦b : ÀR§¤±`®¦¤v¹L¡B¶¢½Í²ö½×¤H«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD