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

[µo°Ý] ¨Ì±ø¥ó§R°£¹Ï¤ù

[µo°Ý] ¨Ì±ø¥ó§R°£¹Ï¤ù

¦b excel Àɮפ¤¡@¦³¨Ç item ªº weekly ·|¨S¦³­È
§Æ±æ¯à§â¨S¦³­Èªº item ©Ò¹ïÀ³ªº¹Ï¤ùµ¹§R°£
¤£¾å±o¸Ó«ç»ò¼g¡H

½ÐÀ°¦£¬Ý¤@¤U¡I¡@ÁÂÁ¡ã

PS. ÀɮפӤj¡A©Ò¥H¥u©ñ´X±i¹Ï¥Nªí¡C

Picture.zip (975.7 KB)

¦^´_ 1# Genie
¸Õ¸Õ¬Ý
  1. Sub DelPic()
  2.     Dim R As Range, x As Picture, D As Object
  3.     Set D = CreateObject("scripting.dictionary") '¦r¨åª«¥ó
  4.     With ActiveSheet
  5.         For Each R In .Range("b3:b" & .[b3].End(xlDown).Row)
  6.             D(R.Value) = R.Offset(, 1).Value     '¦r¨åª«¥ó:key ªºitem
  7.         Next
  8.         For Each x In .Pictures
  9.             If D(x.TopLeftCell.Offset(-1).Text) = "" Then x.Delete
  10.         Next
  11.     End With
  12. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE


¤£¦n·N«ä¡@¦³´X­Ó°ÝÃD

1. ¦³¤@¥y¬Ý¤£¤ÓÀ´¡@"If D(x.TopLeftCell.Offset(-1).Text) = "" Then x.Delete"
     ³o¬O«ü¹Ï¤W¤èªº item ¹ïÀ³¨ìªí®æªº weekly ¬°ªÅ®æªº·N«ä¶Ü¡H

2.  §Ú­Y±N weekly §ï¨ì D Äæ¡@item ¤´¦b B Äæ
      ¦]¦¹±Nµ{¦¡½X§ï¬° For Each R In .Range("D3:D" & .[D3].End(xlDown).Row)
            ¡@¡@¡@¡@¡@        D(R.Value) = R.Offset(, 2).Value
      ¨ä¾l¤£ÅÜ
      ¦ý°õ¦æ¥X¨Ó«o±N©Ò¦³ªº¹Ï¤ù§R°£¡H

½ÐÀ°¦£¬Ý¤@¤U¡@ÁÂÁ¡I

TOP

¦^´_ 3# Genie
TopLeftCell: ¹Ï¶K¤W³Ì³»ºÝ¤Î³Ì¥ªºÝªºCell (储¦s®æ)->¤W¤@¦C:Offset(-1).Text,¬O³o¹Ïªºitem¦r¦ê
1. ¦³¤@¥y¬Ý¤£¤ÓÀ´¡@"If D(x.TopLeftCell.Offset(-1).Text) = "" Then x.Delete"
    ³o¬O«ü¹Ï¤W¤èªº item ¹ïÀ³¨ìªí®æªº weekly ¬°ªÅ®æªº·N«ä¶Ü¡H ¹ïªº

2.§Ú­Y±N weekly §ï¨ì D Äæ¡@item ¤´¦b B Äæ   
   µ{¦¡½X§ï¬° For Each R In .Range("B3:B" & .[B3].End(xlDown).Row)
            ¡@¡@¡@¡@¡@        D(R.Value) = R.Offset(, 2).Value
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ Genie ©ó 2013-3-19 16:03 ½s¿è

¦^´_ 2# GBKEE


¤£¦n·N«ä¡@²Ä¤GÂI¥´¿ù¤F

2.  §Ú­Y±N³Ì¤W¤èªºÁ`ªí weekly §ï¨ì D Äæ¡@item ¤´¦b B Äæ
      ¦]¦¹±Nµ{¦¡½X§ï¬° For Each R In .Range("B3:B" & .[B3].End(xlDown).Row)
            ¡@¡@¡@¡@¡@        D(R.Value) = R.Offset(, 2).Value
      ¨ä¾l¤£ÅÜ
¡@  °õ¦æ¥X¨Ó·|±N©Ò¦³ªº¹Ï¤ù§R°£¡H
¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×¡×
§ï§¹¤~¬Ý¨ì¦^ÂСK¡K

¤£¹L°õ¦æ¥X¨Ó·|±N©Ò¦³ªº¹Ï¤ù³£§R°£­C

TOP

¦^´_ 4# GBKEE


§Ú¸Õ¥X¨Óªºµ²ªG¬O¥u§ï¤W¤èÁ`ªíªº weekly Äæ¦ì¡@©Ò¦³ªº¹Ï³£·|³Q§R°£
¦ý­Y¬O³s¹Ï¤U¤èªí®æªº weekly ¤@°_Åܧ󪺸ܡ@°õ¦æ´N¥¿±`

½Ð°Ý¦³¨S¦³¿ìªk¤£°Ê¹Ï¤U¤èªºªí®æ¡@¥u§ïÁ`ªíªº weekly Äæ¦ì©O¡H

ÁÂÁ¡I

TOP

¦^´_ 6# Genie
½Ð¦Aªþ¤WÀɮ׬ݬÝ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE


¤£¦n·N«ä¡@§Ú¸Õ¦¨¥\¤F

¤£¹L¦³­Ó°ÝÃD·Q°Ý
¬°¤°»ò¡@"For Each R In .Range("b3:b" & .[b3].End(xlDown).Row)"¡@­n B3:B ©O¡H
³o¤@¬q¤£¬O¦b©w¸q¦r¨åªº¤º®e¶Ü¡H
°²¦p§Úªº½d³ò¥u¦³ B3:B7¡@¥u¿é¤J B3:B7¡@¥B±N weekly ªºÄæ¦ì²¾¦Ü D Äæ¡@¬°¤°»ò·|°õ¦æ¿ù»~¡H
¦ý­Y¿é¤J B3:B ´N¨S¦³°ÝÃD©O¡H

Picture.zip (975.68 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2013-3-19 17:57 ½s¿è

¦^´_ 8# Genie
½Ð¬d¬ÝVBA»¡©ú End ÄÝ©Ê   ¶Ç¦^ Rangeª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¨Ó·½½d³ò¤§°Ï°ìµ²§À³BªºÀx¦s®æ¡Cµ¥©ó«ö END+¦V¤WÁä¡BEND+¦V¤UÁä¡BEND+¦V¥ªÁä©Î END+¦V¥kÁä¡C°ßŪ Range ª«¥ó¡C


¬°¤°»ò¡@"For Each R In .Range("b3:b" & .[b3].End(xlDown).Row)"¡@­n B3:B ©O¡H
.[b3].End(xlDown).Row :¶Ç¦^¥Ñ [b3]©¹¤U¨ì³Ì«á¤@­Ó¸ê®Æªº¦C¸¹  ->§AªºÀɮ׶Ǧ^ 7
Range("³o¬O¦r¦ê")-> "b3:b" & 7-> "b3:b7"

§A§ï¬°.Range("b3:b7" & .[b3].End(xlDown).Row) ->"b3:b7" & 7-> "b3:b77" : ÂX¤j¤F½d³ò
BÄ椤B12="A-1",B33="B-1",B53="B-2" ªºDÄæ³£¬OªÅ¥Õ
**D(R.Value) = R.Offset(, 2).Value ¨Ï±oD(KEY)=ªÅ¥Õ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 9# GBKEE


À´¨ä·N«ä¤F¡I¡@«D±`·PÁ¡ã

TOP

        ÀR«ä¦Û¦b : ¤f»¡¦n¸Ü¡B¤ß·Q¦n·N¡B¨­¦æ¦n¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD