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

[µo°Ý] Excel§ì¨ú¦hµ§¸ê®Æ

[µo°Ý] Excel§ì¨ú¦hµ§¸ê®Æ

³Ìªñªø©x°Ý§Ú¦b³øªí¤W­±ªº±Æµ{²§°Ê¥u¯à¬Ý¤@µ§²§°Ê¶Ü?
¦³¨S¦³¿ìªk³s«áÄòªº²§°Ê³£¤@°_±a¥X¨Ó



§Ú·Q¤F«Ü¤[¡A§¹¥þ¤£ª¾¹D­n«ç»ò§â«áÄòªº²§°Ê©ñ¶i¥h
¥Ø«e·Q¨ìªº¤èªk´N¬OÂI¿ï¼Ï¯Ãªí«á¥X²{ªº¼Ë¦¡



½×¾Âªº¤j¤j­Ì
¤p§Ì·Q½Ð¨D¨â¥ó¨Æ±¡À°¦£
(¤@)¦p¦ó§e²{¥XÂI¿ï¼Ï¯Ãªí«á¥X²{ªº¼Ë¦¡
(¤G)Ãö©óªø©xªº­n¨D¬O§_¦³§ó¦nªºªí²{¤è¦¡

±á·|³øªí.zip (175.63 KB)

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2023-6-26 08:35 ½s¿è

¦^´_ 1# cowww


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
«á¾Ç«Øij
1.¥Hµù¸Ñ¤è¦¡§e²{ «áÄòªº²§°Ê,¦p¤U¹Ï




PS:¦pªGªø©x¨Ï¥Îªº¿Ã¹õ°÷¤j¤]¥i¤À³Îµe­±§e²{
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b
³o¼Ëªº¤èªk§Ú¦³·Q¹L¡A¦ý¤£¥i¯à¤@µ§¤@µ§³o¼Ë¶K¤W¥h¡A¤Ó®ö¶O®É¶¡¤F

ÁÙ¬O»¡¥i¥H¥Î¤½¦¡©ÎVBA§¹¦¨µù¸Ñªº¤è¦¡??

PS:ªø©x¨Ï¥ÎNB

TOP

¦^´_ 3# cowww


    ÁÂÁ«e½ú¦^´_
«á¾ÇÂǦ¹©«½m²ß°}¦C.¦r¨å»Pµù¸Ñ,¾Ç²ß¤è®×¦p¤U,½Ð«e½ú°Ñ¦Ò

°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Application.ScreenUpdating = False
Dim Brr, Crr, Z, i&, T$, PH$, FN$, xB As Workbook, Sh As Worksheet
Set Z = CreateObject("Scripting.Dictionary")
PH = ThisWorkbook.Path: FN = "²§°Êªí±Æ§Ç.xlsm"
On Error Resume Next
Set xB = Workbooks(FN): Set Sh = xB.Sheets("²§°Êªí±Æ§Ç")
Brr = Range(Sh.[E1], Sh.[A65536].End(3))
On Error GoTo 0
If xB Is Nothing Then
   Set xB = Workbooks.Open(PH & "\" & FN)
   Brr = Range([²§°Êªí±Æ§Ç!E1], [²§°Êªí±Æ§Ç!A65536].End(3))
   xB.Close 0
End If
For i = 1 To UBound(Brr)
   T = Brr(i, 2): If T = "" Then GoTo i00
   If Z(T) = "" Then
      Z(T) = Brr(i, 3) & " ¢i " & Brr(i, 4)
      Else
         Z(T) = Z(T) & vbLf & Brr(i, 3) & " ¢i " & Brr(i, 4)
   End If
i00: Next
Brr = Range([±M®×!D1], [±M®×!D65536].End(3))
[D:D].ClearComments
For i = 1 To UBound(Brr)
   If Brr(i, 1) = "" Or Z(Brr(i, 1) & "") = "" Then GoTo i01
   Cells(i, 4).AddComment
   Cells(i, 4).Comment.Text Text:=Z(Brr(i, 1) & "")
   Cells(i, 4).Comment.Shape.TextFrame.Characters.Font.Size = 16
   Cells(i, 4).Comment.Shape.DrawingObject.AutoSize = True
i01: Next
Set Z = Nothing: Erase Brr: Set xB = Nothing: Set Sh = Nothing
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 4# Andy2483

¤j¤j±z¯u¬O¤Ó±j¤F
§Ú¥ý°µ¥X¨Óµ¹¥DºÞ¬Ý¬Ý¥L¯à¤£¯à±µ¨ü³o¼Ëªºªí¥Ü¤è¦¡

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½Æ²ß¬Q¤Ñªº¾Ç²ß¤è®×,¤è®×¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Application.ScreenUpdating = False
'¡ô¥O¿Ã¹õ¼È¤£ÀHµÛµ{§Ç°µÅܤÆ
Dim Brr, Z, i&, T$, PH$, FN$, xB As Workbook, Sh As Worksheet
'¡ô«Å§iÅܼÆ($¬O¦r¦êÅܼÆ,&¬Oªø¾ã¼Æ,¨S¦³²Å¸¹ªº¬O³q¥Î«¬ÅܼÆ)
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
PH = ThisWorkbook.Path: FN = "²§°Êªí±Æ§Ç.xlsm"
'¡ô¥OPHÅܼƬO ¥»Àɸê®Æ§¨¦ì§},¥OFNÅܼƬO «ü©wÀɦW(¸ê®Æªí)
On Error Resume Next
'¡ô¥Oµ{§Ç¼È¹J¨ì¿ù»~´NÄ~Äò°õ¦æ¤U­Óµ{§Ç,¤£­n°±¤U¨Ó±Æ¿ù
Set xB = Workbooks(FN): Set Sh = xB.Sheets("²§°Êªí±Æ§Ç")
'¡ô¥OxBÅܼƬO ¬¡­¶Ã¯("²§°Êªí±Æ§Ç.xlsm"),¥OShÅܼƬO¨ä¤u§@ªí
Brr = Range(Sh.[E1], Sh.[A65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~EÄæÀx¦s®æ­È±a¤J°}¦C¤¤
On Error GoTo 0
'¡ô¥Oµ{§Ç«ì´_¹J¨ì¿ù»~´N°±¤U¨Ó±Æ¿ù
'³o¬q¤£±Æ¿ùªºµ{§Ç¬O¬°¤F "²§°Êªí±Æ§Ç.xlsm"³Q¶}±Òªº±¡¹Ò¤U,
'ÅýBrr¥i¥H¸Ë¶i°}¦C­È
'¦pªGÀɮרS¦³³Q¶}±Òªº±¡ªp,µ{§Ç´N·|¸õ¹L³o¨Çµ{§Ç,Ä~Äò¤U¦æ

If xB Is Nothing Then
'¡ô¦pªGxBÅܼÆÁÙ¨S¦³¸Ë¤J¬¡­¶Ã¯("²§°Êªí±Æ§Ç.xlsm")??
   Set xB = Workbooks.Open(PH & "\" & FN)
   '¡ô¥O¶}±Ò«ü©w¸ô®|¤UªºÀÉ®×,¨Ã¥OxBÅܼƬO¦¹¬¡­¶Ã¯
   Brr = Range([²§°Êªí±Æ§Ç!E1], [²§°Êªí±Æ§Ç!A65536].End(3))
   '¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~EÄæÀx¦s®æ­È±a¤J°}¦C¤¤
   xB.Close 0
   '¡ô¥O¬¡­¶Ã¯¤£¦sÀÉÃö³¬
End If
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   T = Brr(i, 2): If T = "" Then GoTo i00
   '¡ô¥OTÅܼƬO °j°é¦C²Ä2ÄæBrr°}¦C­È,¦pªGTÅܼƬOªÅªº!
   '¬O´N¸õ¨ì¼Ð¥Üi00¦ì¸mÄ~Äò°õ¦æ

   If Z(T) = "" Then
   '¡ô¦pªG¥HTÅܼƬdZ¦r¨å±oitem­È¬OªÅ¦r¤¸?
      Z(T) = Brr(i, 3) & " ¢i " & Brr(i, 4)
      '¡ô¬O´N¥O¦bZ¦r¨å¸ÌªºTÅܼÆkey ªºitem´«¦¨·s¦r¦ê
      '·s¦r¦ê:°j°é¦C²Ä3ÄæBrr°}¦C­È³s±µ " ¢i "¦A³s±µ °j°é¦C²Ä4ÄæBrr°}¦C­È,
      '¦¨¬°·s¦r¦ê,©ñ¦^Z¦r¨å¸Ì

      Else
         Z(T) = Z(T) & vbLf & Brr(i, 3) & " ¢i " & Brr(i, 4)
         '¡ô§_«h(TÅܼÆkey ªºitem­È¤w¸g¦³¦r¦ê!)
         '¥Oitem³s±µ´«¦æ¦A³s±µ °j°é¦C²Ä3ÄæBrr°}¦C­È³s±µ " ¢i "¦A³s±µ
         '°j°é¦C²Ä4ÄæBrr°}¦C­È¦¨¬°·s¦r¦ê,©ñ¦^Z¦r¨å¸Ì

   End If
i00: Next
Brr = Range([±M®×!D1], [±M®×!D65536].End(3))
'¡ô¥OBrrÅܼƴ«¸Ëµ²ªGªíªºDÄæÀx¦s®æ­È,¨ÌµM¬O ¤Gºû°}¦C
'PS:Brr«Å§i¬O³q¥Î«¬ÅܼÆ,¥i¥H¥ô·N=´«¸Ë¸ê®Æ ©ÎSet Brr = ª«¥ó

[D:D].ClearComments
'¡ô¥ODÄ檺µù¸Ñ²M°£
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é
   If Brr(i, 1) = "" Or Z(Brr(i, 1) & "") = "" Then GoTo i01
   '¡ô±Æ°£ªÅ®æ©Î¦r¨å¸Ìitem¬OªÅ¦r¤¸ªº¶µ¥Ø,¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
   Cells(i, 4).AddComment
   '¡ô¥Oi°j°é¼Æ¦CDÄæÀx¦s®æ´¡¤Jµù¸Ñ
   Cells(i, 4).Comment.Text Text:=Z(Brr(i, 1) & "")
   '¡ô¥Oi°j°é¼Æ¦CDÄæÀx¦s®æªºµù¸Ñ¤å¦r¬O °j°éBrr°}¦C­È¬dZ¦r¨å±oitem­È
   Cells(i, 4).Comment.Shape.TextFrame.Characters.Font.Size = 16
   '¡ô¥Oi°j°é¼Æ¦CDÄæÀx¦s®æªºµù¸Ñ¤å¦r¤j¤p¬° 16
   Cells(i, 4).Comment.Shape.DrawingObject.AutoSize = True
   '¡ô¥Oi°j°é¼Æ¦CDÄæÀx¦s®æªºµù¸Ñ®Ø¦Û°ÊÁY©ñ
i01: Next
Set Z = Nothing: Erase Brr: Set xB = Nothing: Set Sh = Nothing
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 2# Andy2483

«D±`·PÁÂAndy2483¤j¤jªº¸Ñ´b

ªøºÞ»¡¥L¤ñ¸û³ßÅw³Æµù¨º±i¹Ïªíªº¤è¦¡
§Æ±æ³Æµùªº¦a¤è¥i¥H§ï©ñ¦b¾÷¥x¨º­ÓÄæ¦ì

TOP

¦^´_ 7# cowww


    ³£µù¸Ñ¤F! ½Ð¦Û¤v¸Õ§ï¬Ý¬ÝÅo,¤@°_¾Ç²ß
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 6# Andy2483

¨D±ÏAndy2483¤j¤j
¿ù»~°T®§.PNG


§Ú¦³±N¸ê®Æ§¨ªº¸ô®|§ï¦¨¥H¤Uªº¼gªk
¤£ª¾¹D¬O¤£¬O¦]¬°³o¼Ë¾É­PµLªk°õ¦æ
PH = "\\shl-group.com\dept\MFMG\¹ï¥~³æ¦ì¶}©ñ¸ê®Æ\·|ij«Ç¼Ò¨ã°lÂܸê°T\³Æ¥÷": FN = "¤Å§R«æ¥ó¤½¦¡.xlsm"

TOP

¦^´_ 9# cowww

¨D±Ï¤]¨S¥Î
»·¤ô±Ï¤£¤F
¦Û¤vªºÀô¹Ò¦Û¤v¤~¯à¸Õ,¦h¸Õ´X¦¸§a
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¤£­nÀH¤ß©Ò±ý¡A­nÀH¤ß±Ð¨|¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD