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

Genie

¦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)

¤U¸ü¦¸¼Æ: 9

2013-3-19 11:31

GBKEE

¦^´_ 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

Genie

¦^´_ 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

GBKEE

¦^´_ 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

Genie

¦^´_ 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

Genie

¦^´_ 4# GBKEE


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

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

ÁÂÁ¡I

GBKEE

¦^´_ 6# Genie
½Ð¦Aªþ¤WÀɮ׬ݬÝ

Genie

¦^´_ 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)

¤U¸ü¦¸¼Æ: 6

2013-3-19 17:04

GBKEE

¦^´_ 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)=ªÅ¥Õ

Genie

¦^´_ 9# GBKEE


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