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

¥X³f³æ«Ø¥ß

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

¦^´_ 5# cclo0728


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç´£¨Ñ¥t¤@ºØ¤èªk¨Ñ«e½ú°Ñ¦Ò
¤µ¤Ñ²ß±o°}¦C­Ë¤J¦r¨å»P¦Ü¦r¨å¤¤¨ú¥X°}¦C
½m²ß°}¦C»y¦r¨å

'§R°£µ²ªGªí¸ê®Æ!¦A±a¤J·s¸ê®Æ
Option Explicit
Sub test_A()
Dim Arr, T, xD, xA, xB
'¡ô«Å§iÅܼÆ

Set xD = CreateObject("Scripting.Dictionary")
'¡ô¥OxD¬O¦r¨å

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

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

T = xA.Cells(Rows.Count, 3).End(3).Row - 4
'¡ô¥OT¬O"¸ê®Æ¿é¤J"ªí­n±a¤J "¥X³f¸ê®Æ"ªíªº¦C¼Æ

Arr = xA.Cells(5, 2).Resize(T, 6)
'¡ô¨Ó·½ªí¸ê®Æ­Ë¤JArr°}¦C

xD(1) = Arr
'¡ôArr°}¦C­Ë¤J¦r¨å

xD(2) = xA.Cells(2, 1)
'¡ô«È¤á¦WºÙ­Ë¤J¦r¨å

xD(3) = xA.Cells(2, 2)
'¡ô³æ¸¹­Ë¤J¦r¨å

xB.UsedRange.Offset(1, 0).EntireRow.Delete
'¡ô¸ê®Æ§R°£

xB.[C2].Resize(UBound(Arr), 6) = xD(1)
'¡ô±a¥X°}¦C©ñ¤Jµ²ªGªí

xB.[A2].Resize(T, 1) = xD(2)
'¡ô±a¥X«È¤á¦WºÙ©ñ¤Jµ²ªGªí

xB.[B2].Resize(T, 1) = xD(3)
'¡ô±a¥X³æ¸¹©ñ¤Jµ²ªGªí

End Sub

TOP

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

¥»©«³Ì«á¥Ñ 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:
2022-10-08_155513.JPG
2022-10-8 16:02


¥X³f¸ê®Æ:
2022-10-08_155528.JPG
2022-10-8 16:03


¸ê®Æ®w:
2022-10-08_155542.JPG
2022-10-8 16:03

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

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

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

¦^´_ 18# cclo0728


    «e½ú¦­¦w
½d¨Ò½Ð¸Õ¸Õ¬Ý
test_1_20221012_1.zip (31.36 KB)

¶}©l±¡¹Ò:
2022-10-12_075515.JPG
2022-10-12 08:02


[B2]¥ªÁä§Ö«ö¨â¤U:
2022-10-12_075537.JPG
2022-10-12 08:02


«ö ½Æ»s¨ì¥X³f¸ê®Æ¶s ªº¥X³f¸ê®Æªíµ²ªG
2022-10-12_075634.JPG
2022-10-12 08:03


¸ê®Æ¿é¤Jªíªºµ²ªG:
2022-10-12_075701.JPG
2022-10-12 08:05


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

'¡õ¼Æ¦r»P¦r¦ê¥i¥H¥Ñ¤@¶}©lªº«Å§i´N¨M©w³oÅܼƬO¦r¦ê©Î¼Æ¦r!
Dim Arr, T, xA, xB, 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§@ªí

M1 = xB.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

¦^´_ 24# cclo0728


    ÁÂÁ«e½ú¦^ÂÐ
O1,O2¬O¤W¤@ª©¥»º|§R°£ªº!¤£¼vÅT°õ¦æ!

¤u§@ªÅÀɽm²ß°}¦C»P¦r¨å!
¥H«á¥i¥H¤j´TÁYµuµ{¦¡°õ¦æ®É¶¡!

ÁÂÁ«e½ú´£¥X¥DÃD»P½d¨Ò,«á¾Ç¥i¥H¾Ç¨ì«Ü¦h!
¾Ç¥Í´N¬O¸Ó¶Ô³Ò½m²ß!
²q¦Ñ®v·|¦Ò¤°»ò? ¤]«Ü¦³½ì!

ÃD¥Ø©Î½d¨Ò¦Ò±o¤Ó²³æ!¤]­n½ÆÂø¤Æ!
¥HÀË´ú¦Û¤vªº«ä¦Ò¬O§_©P¥þ!

¾Ç¤Q´X¦~¤F!¼ö±¡¨ÌÂÂ!
«á¾ÇÁy¥Ö«p!¤ß±oµù¸Ñ¤]¤£¾á¤ß«e½ú«ü¥¿©Î´£¿ô !
¤£¹H¤Ïª©³W!´N¦n¤F!
¤H¥Íªº¶Q¤H´N¦b µo©«/°Ñ»P/¦^´_¥DÃD ¸Ì

TOP

        ÀR«ä¦Û¦b : ªY½à§O¤H´N¬O²øÄY¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD