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

¥X³f³æ«Ø¥ß

¦^´_ 18# cclo0728


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

¶}©l±¡¹Ò:


[B2]¥ªÁä§Ö«ö¨â¤U:


«ö ½Æ»s¨ì¥X³f¸ê®Æ¶s ªº¥X³f¸ê®Æªíµ²ªG


¸ê®Æ¿é¤Jªíªºµ²ªG:


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

¦^´_ 19# Andy2483
©êºp¥´ÂZ¨ì§A³oÃä¡AÅý§Aªá®É¶¡¼gµ{¦¡
ÁÙ¬O«D±`·PÁÂ

TOP

¦^´_ 20# Andy2483

¥X³f¸ê®Æ¬°¨C¤é¥X³f©ú²Ó¡A©Ò¦³¥X³fªº¸ê®Æ¥þ³¡«Ø¥ß¦b¤@­Ó¤À­¶
¦]¤£»Ý­n¤Ó½ÆÂø¡A¥[¤u¼tªº¿Ë¤H¥u·|°ò¦excel
©Ò¥H¡A¹ï¥¦­Ì¨ÓÁ¿¶V²³æ¶V¦n

TOP

¦^´_ 21# Andy2483

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ÅܼÆ

·PÁ¡AÅý©p¦Ê¦£¤§¤¤ªá®É¶¡À°¦£¡A«D±`ÁÂÁÂ
¥t¥~¡A½Ð±ÐO1&O2¬O«Å§i­þºÝªºÅܼÆ?

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

¦^´_ 25# Andy2483
§A¹ê¦b¤Ó«È®ð¤F¡A«D±`ÁÂÁ§A

TOP

¦^´_ 21# Andy2483

½Ð°Ý³æ¸¹B2¬O¨Ï¥Î¤°»ò¼Ëªº¤è¦¡¹F¦¨¹³§A³o¼Ëªº³]©w?

TOP

¦^´_ 27# cclo0728


    ¹B¥ÎIJµo:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
   If .Address = "$B$2" Then
      Call ·j´M¥X³f¸ê®Æ¨M©w³æ¸¹
      
      Cancel = True
   End If
End With
End Sub

TOP

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

¦^´_ 27# cclo0728


    «Øij«e½ú:¸ê®Æ¿é¤J·J¾ã¦Ü¥X³f¸ê®Æ()¸Ì¦h­Óµ{§Ç!
Àˬd­«½Æ!
¾Þ§@ªÌ¤£ª¾±¡¦³­«½Æ§Ç¸¹:


¥X²{´£¥Ü:


¸õ¦Ü¥X³f¸ê®Æ­«½ÆÀx¦s®æ³B


¥¿½T¤£­«½Æ§Ç¸¹:


¥¿½T·J¾ã¦Ü¥X³f¸ê®Æªí


Option Explicit
Public ERR&
Sub ¸ê®Æ¿é¤J·J¾ã¦Ü¥X³f¸ê®Æ()
Call Àˬd¥X³f¸ê®Æ_§Ç¸¹­«½Æ

If ERR = 1 Then
'¡ô¦pªG"¥X³f¸ê®Æ" ¤u§@ªí§ä¨ì¬Û¦P§Ç¸¹
   ERR = 0
   '¡ô³o­Ó¸óµ{¦¡ªºÅܼÆÂk¹s
   Exit Sub
End If
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.Cells(Rows.Count, "C").End(3).Offset(1, 0).Resize(UBound(Arr), 6) = xD(1)
'¡ô±a¥X°}¦C©ñ¤Jµ²ªGªí

xB.Cells(Rows.Count, "A").End(3).Offset(1, 0).Resize(T, 1) = xD(2)
'¡ô±a¥X«È¤á¦WºÙ©ñ¤Jµ²ªGªí

xB.Cells(Rows.Count, "B").End(3).Offset(1, 0).Resize(T, 1) = xD(3)
'¡ô±a¥X³æ¸¹©ñ¤Jµ²ªGªí

xA.[B2].Interior.ColorIndex = 3
xA.[B2].Font.ColorIndex = 2

Sheets("¥X³f¸ê®Æ").Activate
End Sub
Sub Àˬd¥X³f¸ê®Æ_§Ç¸¹­«½Æ()
Dim xA, xB, BFind As Range
'¡ô«Å§iÅܼÆ
Set xA = [¸ê®Æ¿é¤J!B2]
'¡ô¥OxA¬O "¸ê®Æ¿é¤J" ¤u§@ªí [B2]
Set xB = [¥X³f¸ê®Æ!B:B]
'¡ô¥OxB¬O "¥X³f¸ê®Æ" ¤u§@ªí BÄæ
Set BFind = xB.Find(xA, LookAt:=xlWhole)
'¡ô´M§ä ¥X³f¸ê®Æ!B:B ¤º®e¥þ¬Û¦PÀx¦s®æ
'¡ô(xA, LookAt:=xlPart) ¬O³¡¤À¬Û¦PÀx¦s®æ
If Not BFind Is Nothing Then
'¡ô¦pªG¦³§ä¨ì
   MsgBox "¥X³f¸ê®Æ¤w¸g¦³: " & xA & " §Ç¸¹!"
   Sheets("¥X³f¸ê®Æ").Activate
   '¡ôµe­±¸õ¨ì "¥X³f¸ê®Æ" ªí
   BFind.Activate
   '¡ô¿ï¨ú§ä¨ìªº¨º­ÓÀx¦s®æ
   ERR = 1
   '¡ô¬O¤@­Ó¸óµ{¦¡ªºÅܼÆ,¦pªG§ä¨ì ¥OERR = 1
End If
End Sub

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-10-14 08:09 ½s¿è

¦^´_ 27# cclo0728


    «e½ú¦­¦w
«á¾Ç¦­¤W½Æ²ß¤F©î¸Ñªø§Ç¸¹¼W¥[¨âÄæ©ñ¤J¤é´Á»Pµu§Ç¸¹
½m²ß°}¦C
´£¨Ñ«e½ú°Ñ¦Ò!
­ì¥X³f¸ê®Æªø§Ç¸¹¿z¿ï:


°õ¦æµ{¦¡«á²£¥Í·sÀÉ®×¥i¿z¿ï¤é´Á:


·sÀÉ®×¥i¿z¿ïµu§Ç¸¹:


¥H¤U½Æ²ßªºµ{¦¡½X¨Ñ°Ñ¦Ò:
Option Explicit
Sub ªø§Ç¸¹Âà_¤é´Á_µu§Ç¸¹()
Dim Arr, i&, xB, N&, D1 As Date
'¡ô«Å§iÅܼÆ:D1 ¬O¤é´Á,N¬O¼Æ¦r

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

Arr = xB.Range(xB.[J1], Cells(xB.UsedRange.EntireRow.Count, 1))
'¡ô¥OArr¬O"¥X³f¸ê®Æ" ¤u§@ªí A:GÄ椧¶¡¦³¨Ï¥Î¦Cªº°Ï°ìÀx¦s®æ­È

For i = 2 To UBound(Arr)
'¡ô³]°j°é©î¸ÑBÄ檺ªø§Ç¸¹

   D1 = "20" & Mid(Arr(i, 2), 1, 2) & "/" & Mid(Arr(i, 2), 3, 2) & "/" & Mid(Arr(i, 2), 5, 2)
   '¡ô "20",¥[ªø§Ç¸¹²Ä1­Ó¦r¶}©l¨ú¨â¦r¤¸="22"
   ',¦A¥[ªø§Ç¸¹²Ä3­Ó¦r¶}©l¨ú¨â¦r¤¸="10"
   ',¦A¥[ªø§Ç¸¹²Ä5­Ó¦r¶}©l¨ú¨â¦r¤¸="08"
   'D1="2022/10/08"¦r¦êÂà¤Æ¬°¤é´Á,¦]D1«Å§i¬°¤é´Á

   
   Arr(i, 9) = D1
   '¡ô§âD1¤é´Á©ñ¤JArrªº²Ä9Äæ¦ì¸m
   
   N = Right(Arr(i, 2), 3)
  '¡ô¥ON¬O±`§Ç¸¹ªº¥kÃä3­Ó¦r¤¸¦r¦êÂà¼Æ¦r,¦]N«Å§i¬°¼Æ¦r
   
   Arr(i, 10) = N
   '¡ô§âN¼Æ¦r©ñ¤JArrªº²Ä10Äæ¦ì¸m
   
Next
Workbooks.Add
'¡ô¶}¤@­Ó·sªºÀÉ®×

[A1].Resize(UBound(Arr), 10) = Arr
'¡ô§âArr°}¦Cªº¸ê®Æ±q[A1]¶}©l­Ë¤J·s¤u§@ªíªº¦s®æ

[I1] = "¥X³f¤é´Á"
[J1] = "·í¤é§Ç¸¹"
Cells.Columns.AutoFit
'¡ô¦Û°Ê½Õ¾ãÄæ¼e

Cells.Rows.AutoFit
'¡ô¦Û°Ê½Õ¾ã¦C°ª

[2:2].Select
ActiveWindow.FreezePanes = True
'¡ô²Ä¤G¦C¥H¤WÀx¦s®æ­áµ²µ¡®æ

[A1].Select
[A1].AutoFilter
'¡ô³]©w¿z¿ï

Cells.Borders.LineStyle = xlContinuous
'¡ôÅã¥Ü®æ½u
End Sub

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD