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

[µo°Ý] excel ¦Û°Ê¦X¨ÖÀx¦s®æ

¦^´_ 18# Andy2483

·PÁÂAndy2483«e½úªº«ü¾É
Åý¤p§Ì§Ú¥i¥H¦³¦h¦h¾Ç²ßªº¾÷·|¡I
«D±`·PÁ¡I

«Ý«áÄò¤p§Ì²z¸Ñ«á
­Y¦³°ÝÃD¦A¸ò±z´£¥X
¨ì®É­Ô¦A³Â·Ð±z¤F¡I·PÁ±z¡I

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-1 14:37 ½s¿è

¦^´_ 20# ¬PªÅÉ@¦ÐÁl


    ¥i¥H°Ñ¦Ò : ½Ð°Ývbaªº¤º¥~®Ø½u ¥DÃD
http://forum.twbts.com/thread-9862-1-1.html
¤@°_¾Ç²ß!

TOP

¦^´_ 22# Andy2483

¦nªº
·PÁ±z´£¨Ñªº°Ñ¦Òºô­¶¡I
Åý«á¾ÇªáÂI®É¶¡²z¸Ñ¤@¤U¡A¦A¶i¦æÅܤơC
·PÁ¡I

TOP

¥Î¤u§@ªí¨Æ¥óIJµo¦X¨Ö¤ë¥÷//
Xl0000314-1.rar (22.06 KB)

TOP

¦^´_ 24# ­ã´£³¡ªL

·PÁª©¥D­ã´£³¡ªLªº¦^ÂÐ
¤£¦n·N«ä
¥Ø«e«á¾Ç¿n¤ÀÁÙ®t£¸¨Ç
«Ý§Ú¦A§V¤O¤@¤U
«K¥i¤U¸ü±zªºÀɮרӾDzß

·PÁ±z¡I

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-19 08:52 ½s¿è

¦^´_ 24# ­ã´£³¡ªL


    ÁÂÁ«e½ú«ü¾É
³o©«¾Ç¨ì«Ü¦hª¾ÃÑ,«á¾Ç¨S¦³¤Ñ¤~ªº¤Ñ¤À,¥u¯à¾a¶Ô¾Ç²ß¶Ô½m²ß
¾Ç²ßµ²ªG»P¤ß±oµù¸Ñ¦p¤U,½Ð¦P¾Ç¥iÂǦ¹©«¦A´£¥X¤£¦P¬Ýªk©ÎºÃ°Ý,
½Ð«e½ú­Ì¦A«ü¾É
¹Lµ{:
1.jpg
2022-12-19 08:42


µ²ªG:
2.jpg
2022-12-19 08:42


¤u§@ªí¼Ò²Õ:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'¡ô¥H¤U¬OÃö©óIJµo(Àx¦s®æ­È½s¿è)«áªºµ{§Ç
     If .Address <> "$E$5" Then Exit Sub
     '¡ô¦pªGIJµo®æªº¦ì§}¤£¬O "$E$5",´Nµ²§ôµ{¦¡°õ¦æ
     If IsDate(.Value) Then Call ¦X¨Ö¤ë¥÷
     'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/isdate-function
     'IsDate()¦pªG¹Bºâ¦¡¬O¤é´Á©Î¬O¥i¿ëÃѬ°¦³®Äªº¤é´Á©Î®É¶¡¡A·|¶Ç¦^ True¡A§_«h·|¶Ç¦^ True ¡C§_«h¡A¥¦·|¶Ç¦^ False¡C
     'ifªº±ø¥ó¦¨¥ß!´N°õ¦æ ¦X¨Ö¤ë¥÷ °Æµ{¦¡
     'IJµo[E5]¥i¥H±a°_³sÂê¤ÏÀ³,¤½¦¡§ó·s,¤ë¥÷¤]§ó·s¤F

End With
End Sub

Module1:
Sub ¦X¨Ö¤ë¥÷()
Dim xR As Range, xA As Range, m$, m1$, m2$
'¡ô«Å§iÅܼÆ(xR,xA)¬OÀx¦s®æÅܼÆ,(m,m1,m2)¬O¦r¦êÅܼÆ
Application.ScreenUpdating = False
'¡ô¿Ã¹õµe­±¼È¤£¸òµÛµ{§ÇÅܤưõ¦æµ²ªG
With Range("e4", Cells(5, Columns.Count).End(xlToLeft)(0))
'¡ô¥H¤U¬OÃö©óÀx¦s®æ[E4]¨ì²Ä5¦C³Ì¥kÃ䦳¤º®eÄæ¦ìÀx¦s®æªºµ{§Ç
     .UnMerge:  .ClearContents
     '¡ô¨ú®ø¦X¨ÖÀx¦s®æ :²M°£Àx¦s®æ¤º®e
     For Each xR In .Cells
     '¡ô³]¶¶°j°é!¥OxR ¬O³o¨ÇÀx¦s®æ¤§¤@,±q«e­±½ü¨ì«á­±(¥ª¦Ü¥k)
         m1 = Format(xR(2), "m")
         '¡ô¥Om1¬O°j°éxR ¤U¤è1®æÀx¦s®æÅܤƬ°¦r¦ê(³W«h¬O:¨ú¤é´Áªº¤£¸É0¤ë¥÷)
         '¦Pm1 = Format(xR.ITEM(2, 1), "m")
'https://learn.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/format-function-visual-basic-for-applications

         m2 = Format(xR(2, 2), "m")
         '¡ô¥Om2¬O°j°éxR ¤U¤è1¦C¥ª¤è1ÄæÀx¦s®æÅܤƬ°¦r¦ê(³W«h¬O:¨ú¤é´Áªº¤£¸É0¤ë¥÷)
         '¦Pm2 = Format(xR.ITEM(2, 2), "m")

         If m1 <> m Then
         '¡ô¦pªGm1<>m ,m¦r¦êÅܼƪºªì©l­È¬O"",³o¼Ëªº§PÂ_¦¡¦b²Ä¤@®æ®É±ø¥ó´N·|¦¨¥ß!
         '³o¬O«á¾Ç­n¾Ç²ßªº¤èªk!¬JµM³£ª¾¹D­n«Å§im¦r¦ê,¬°¤°»ò¤S¤£·|Åým¸òm1¤ñ´N·|±o¨ìµ²ªG!!
         '»Ý­n¸õ²æ¦r¦êÅܼƤ@©w­nµ¹¤@­Ó¦r¦ê¤~®³¨Ó°µÅÞ¿è§PÂ_ªº·Qªk!
         '¶Ô¾Ç²ß¶Ô½m²ß¬Ý¬Ý¥i§_¸õ²æ,ÁÂÁ«e½ú«ü¾É

            m = m1:  Set xA = xR
            '¡ôif±ø¥ó¦¨¥ß!´N¥Om=m1 ,¥OxAÀx¦s®æÅܼƬO xR
            xR = Application.Text(xR(2), "[DBNum1]m¤ë")
            '¡ô¥OxRÀx¦s®æÅܼƭȬO °j°éxR¤U¤è1®æÀx¦s®æÅܤƬ°¤p¼g¤ë¥÷¦r¦ê
            'Text()·|¶Ç¦^«ü©w¤§ª«¥óªº®æ¦¡¤Æ¤å¦r¡C °ßŪªº String
            '[DBNum1]:¤¤¤å¤p¼g
            '[DBNum2]:¤¤¤å¤j¼g

         End If
         If m2 <> m Then Range(xR, xA).Merge
         '¡ô¦pªGm2 <> m,´NÅýÀx¦s®æ(xR§À®æ, xAÀY®æ)¤§¶¡ªºÀx¦s®æ¦X¨Ö
     Next
    .Borders.LineStyle = 1
     '¥O¾ã­ÓÀx¦s®æ¶°½d³ò®æ½u¬O²Ó¹ê½u
End With
End Sub
'¡ô¿Ã¹õµe­±¦b°õ¦æµ²§ô«á¦Û°ÊÅã¥Ü³Ì«áµ²ªG
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 26# Andy2483

  ÁÂÁ«e½ú¦Û¦æ¾Ç²ß®É¤]±Nµù¸Ñ©ñ¤W¨Ó
Åý«á¾Ç¥i¥H§ó¦n²z¸Ñ¾Ç²ß
«D±`·PÁ¡I

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD