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

[µo°Ý] ¶ñ¤J¼t¯Ê&µù¸Ñ

[µo°Ý] ¶ñ¤J¼t¯Ê&µù¸Ñ

¤j¤j¦n,
§Ú·Q±N ³Ì·s®w¦sB.xlsxªº ¼t¯Ê ¶ñ¤J ¤ë®w¦sªí.xlsx¤¤ ¨Ã¥[¥Hµù¸Ñ,½Ð°Ý¥H¤U»Ý¨D¸Ó¦p¦ó¼g?
µ{¦¡1
1) ¤ë®w¦sªí.xlsxªºsheet¥H¨C¤ëªº¤Ñ¼Æ©R¦W
2) ±N³Ì·s®w¦sB.xlsxªº­¸¤ñsheet BJ4:CB¸ê®Æ
3) ¶ñ¤J¤ë®w¦sªí.xlsx¤¤,¹ïÀ³ ³Ì·s®w¦sB.xlsx H1ªº¤é´Á
¨Ò¦p¡GH1 ¬°4/16,«h¹ïÀ³¤ë®w¦sªí.xlsxªºsheet 16
4) ³Ì·s®w¦sB.xlsxªº­¸¤ñBJ4:CBÀx¦s®æ<>"",«h¶ñ¤Jsheet 16 J4:Y
EX 1:¤ë®w¦sªí.xlsx sh16  J4­È¬°1266
¹ïÀ³ ³Ì·s®w¦sB.xlsx ªºBJ4Àx¦s®æ ­È¬°34
«hJ4=1266-34

EX 2:¤ë®w¦sªí.xlsx sh16  L7­È¬°198
¹ïÀ³ ³Ì·s®w¦sB.xlsx ªºBL7Àx¦s®æ ­È¬°""
«hL7Àx¦s®æ¤£§ïÅÜ....¨Ì¦¹Ãþ±À

µ{¦¡2
µ{¦¡1ªº¼t¯Ê¸ê®Æ¶ñ¤J«á
±N©Ò¦³¶ñ¤J¼t¯Ê­Èªº­È¦s®æ,´¡¤Jµù¸Ñ"¼t¯ÊXX"
EX 1:¤ë®w¦sªí.xlsx sh16  J4=1266-34
«hµù¸Ñ¬°  ¼t¯Ê34

¶ñ¤J¼t¯Ê.rar (842.49 KB)

Dear¤j¤j,
½Ð«ü¾É µ{¦¡2
¥¦¥i¥H¼W¥[ªÅ¥Õµù¸Ñ,¦ýµLªk±NWorkbooks("³Ì·s®w¦sB.xlsx").Sheets("­¸¤ñ")ªº¼t¯Ê­È¸ü¤Jµù¸Ñ¤¤,
¦Ó¥B¸ü¤Jªºµù¸ÑµLªk¨Ì Sheets("­¸¤ñ")ªº¼t¯Ê­È¦ì¸m¸ü¤J,µù¸Ñ¶ñº¡¤F ¤ë®w¦sªí.xlsx Range("J4:Y")
¥B§Æ±æµù¸Ñªº®Ø®Ø¤j¤p,¥i¥H¨Ìµù¸Ñ¤º®e¦Û°Ê¤j¤p,¥Ø«eªº¤Ó¤j¤F.
  1. Sub ¼t¯Êµù¸Ñ()
  2. Dim PH$, FN$, rng As Range, xb As Workbook, Sh As Worksheet, i As String
  3. Application.ScreenUpdating = False
  4. Application.DisplayAlerts = False       '¤@¯ë´£Äµ¥Ü°T®§Ãö³¬
  5. Application.Calculation = xlManual     '¤â°Ê­pºâ
  6. i = Format(Date, "D")
  7. FN = "¤ë®w¦sªí.xlsx"  '¥ØªºÀÉ
  8. PH = "T:\0_¦Û­qªí³æ\¨ä¥Lªí³æ\¥­¤é®wªí\" '¥Øªº
  9. On Error Resume Next: Set xb = Workbooks(FN): On Error GoTo 0 'ÀˬdÀɮ׬O§_¤w¶}±Ò(ÁקK·í¾÷)
  10. If xb Is Nothing Then Set xb = Workbooks.Open(PH & FN) '¥Øªº­Y¥¼¶}±Ò,«h¶}±Ò¤§
  11. Set Sh = Workbooks("³Ì·s®w¦sB.xlsx").Sheets("­¸¤ñ")
  12. With Sh
  13.     xRow = Cells(Cells.Rows.Count, "F").End(xlUp).Row
  14.     Set sRng = Range("BJ4:BY" & xRow)  '¨Ó·½:¼t¯Ê½d³ò
  15. End With

  16. With xb.Sheets(i)
  17. xb.Sheets(i).Activate
  18. xRow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
  19. For Each rng In Range("J4:Y" & xRow) '­n¼g¤Jªº½d³ò,¥Øªº­q³æ½d³ò
  20. On Error Resume Next
  21. If sRng <> "" Then  'Àx¦s®æªº­È¤£¬° ªÅ¥Õ
  22. Set cmt = rng.Addcomment  '±NÀx¦s®æªº­È¼g¤Jµù¸Ñ¤¤
  23. cmt.Text "¼t¯Ê*" & sRng.Value
  24. With rng.Comment.Shape.TextFrame.Characters.Font
  25. .AutoSize = True
  26. .Name = "²Ó©úÅé"
  27. .Size = 10
  28. End With
  29. End If
  30. Next rng
  31. End With
  32. Application.ScreenUpdating = True   '¥´¶}«Ì¹õ§ó·s
  33. End Sub
½Æ»s¥N½X

TOP

¦Ò¼{¨âÀɪº[°Ó«~]¤Î[¾P°âÂI]¦WºÙ©Î¦ì¸m¥i¯à¤£¤@­P, ©Ò¥H¼g±o½ÆÂø,
¦³°ÝÃD¦Û¦æ¥h­×§ï:
¶ñ¤J¼t¯Êv01.rar (163.8 KB)

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-4-25 21:58 ½s¿è

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

­ã¤j¦n,
¶ñ¤J"¤ë®w¦sªí"ªº¼t¯Ê­È,¥²¶·¥Î­ì¨Óªº­È-¼t¯Ê
¤£¯àª½±µÅܦ¨­È,½Ð°Ý³o­Ó³¡¥÷­n«ç»ò§ï?
EX:
­ì­q³æ­È¬°450
¼t¯Ê20
«h¬Û¹ïÀ³Àx¦s®æ=450-20

TOP

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

& ²Ä2­Ó°ÝÃD
¦]µ{¦¡»P¥­¤éÀɮפ£©ñ¦b¤@°_ªº,©Ò¥H§Úµy­×§ï¥H¤Uµ{¦¡,¦ýµLªk¹B§@:
P.S. ­ì­q³æ­È ¹ïÀ³Àx¦s®æ ­YµL¼t¯Êµo¥Í,«h «O«ù²{ª¬ (¥u¦³­ì­q³æ­È)
  1. Sub ¼t¯Êµù¸Ñ()
  2. Dim Sht As Worksheet, PH$, FN$, xB As Workbook, xS As Worksheet
  3. Dim xD, xR As Range, Arr, R&, C&
  4. Application.ScreenUpdating = False
  5. Application.Calculation = xlManual
  6. Set Sht = Sheets("­¸¤ñ")
  7. FN = "¤ë®w¦sªí.xls": PH = "T:\½d¨Ò\"
  8. On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
  9. If xB Is Nothing Then Set xB = Workbooks.Open(PH & FN)
  10. 'ThisWorkbook.Activate
  11. xB.Activate
  12. On Error Resume Next: Set xS = xB.Sheets(Day(Sht.[H1]) & ""): On Error GoTo 0
  13. If xS Is Nothing Then MsgBox "¡e¤é®w¦sªí¡f¤£¦s¦b! ": Exit Sub
  14. If xS.[J2] = "***" Then MsgBox "¥»¤é®w¦s¤w¦©°£! ": Exit Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-4-26 10:22 ½s¿è

¦^´_ 4# PJChen


1)  xR = "=" & Val(xR) & "-" & Arr(i, j)
    ©Î xR = "=" & IIf(Val(xR) = 0, "", xR) & "-" & Arr(i, j)

2)  FN = "¤ë®w¦sªí.xlsx"

TOP

¥»©«³Ì«á¥Ñ PJChen ©ó 2020-4-26 17:34 ½s¿è

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

­ã¤j,
§Ú¬O¥Î§A¦^ÂЪºÀÉ®×´ú¸Õ,ÀÉ®×Ãþ«¬.xls, ¦]µ{¦¡»P¥­¤éÀɮפ£©ñ¦b¤@°_ªº,©Ò¥H§Ú­×§ï¬õ¦r³¡¥÷,¦ýµLªk¹B§@,¦A³Â·Ð«üÂI¡I
Set Sht = Sheets("­¸¤ñ")  '³o¸Ì¥d¦í¤F,µLªk°õ¦æ
FN = "¤ë®w¦sªí.xls": PH = "T:\½d¨Ò\"
On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0
If xB Is Nothing Then Set xB = Workbooks.Open(PH & FN)
'ThisWorkbook.Activate
xB.Activate  '³o¸Ì¤]¦³°ÝÃD,µLªk°õ¦æ

TOP

¦^´_ 7# PJChen


Set Sht = ActiveSheet

TOP

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

­ã¤j,
ÁÙ¬O¤£¦æ,³o¼Ë·|¶Ã§ì¤u§@ªí,§Ú§âµ{¦¡¥Î§Ú²{¦æªº°µªk©ñ¦bMacro_1
³Â·ÐÀ°¦£¬Ý¤U...·PÁÂ

½d¨Ò0426.rar (259.02 KB)

TOP

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

­ã¤j,
§Ú§ï¦n¤F,ÁÂÁ¡I

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD