- ©«¤l
- 106
- ¥DÃD
- 35
- ºëµØ
- 0
- ¿n¤À
- 146
- ÂI¦W
- 0
- §@·~¨t²Î
- Window 7
- ³nÅ骩¥»
- 2007
- ¾\ŪÅv
- 20
- ©Ê§O
- ¤k
- µù¥U®É¶¡
- 2011-8-30
- ³Ì«áµn¿ý
- 2024-3-26
|
VBA CODEªºì©l¨Ó·½ : https://alice57.pixnet.net/blog/post/23273559
¤j®a¦n, §Ú¦bºô¸ô§ä¨ìÓ¶K¤JȪºVBA, קï«á, ¦³´XÓ¦a¤è¤£©ú¥Õ, ¦A½Ð±Ð¤@¤U¦U¦ì
1. µµ¦â¦r¸Ìªº"0 + 16" ³oÓ¬O¤°»ò·N«ä©O?
2. ¦bCOPY¨ìdSht , ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼, ³oÓn«ç»ò§ï©O?
* §Ú¬O§ï¥Î
dSht.Range("B2:B" & y).Copy
MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
¦³¦¨¥\, ¥u¬O·Qª¾¹D¦bì©lªº.Range("B2:B" & y).Copy MySht.[C2] <---- ¦b³oÓCODE¤Un«ç»ò¼g, ¥u¶K¤JÈ´N¦n, ¤£n°Ê¨ì®æ¦¡
·PÁ¦U¦ì!
Sub ¶KÂd¸¹()
Dim MySht As Worksheet, dSht As Worksheet, y&
Set MySht = Sheets("¥X³f«á")
Set dSht = Sheets("¥X³f©ú²Ó")
With dSht
MySht.Range("C2:C10000").Clear '²M°£ì¦³¸ê®Æ
dSht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
y = dSht.Range("B65536").End(xlUp).Row
If y < 2 Then MsgBox "¡°¨Ó·½ªí¡e¢ÐÄæ¡fµL¸ê®Æ¡I¡@", 0 + 16: Exit Sub
'¡ô¥ý¸Ñ°£¿z¿ï¡A¦AÀË´ú¸ê®Æ§À¦C¦C¸¹¡AY¿z¿ï¤¤¡AÀË´ú¦C¸¹·|¦³»~®t
If WorksheetFunction.CountIf(dSht.Range("A2:A" & y), "1") = 0 Then
MsgBox "¡°¨Ó·½ªí¡eAÄæ¡fµL¡e1¡f¿z¿ïÈ¡I¡@", 0 + 16: Exit Sub
End If
'¡ôÀË´ú¡e1¡f¼Æ
'ª`·N¡G"~*"¤~¯à³æ¿ï¡e*¡f¡A§_«h¨ä¥¦¤å¦r¡]¨Ò¦p¡GA.B.C¡^¤]·|¿z¿ï¶i¨Ó
dSht.Range("$A$1:$AB$1500").AutoFilter Field:=1, Criteria1:="1"
dSht.Range("B2:B" & y).Copy
MySht.Range("C2:J1000").PasteSpecial Paste:=xlPasteValues
'ì©lªºCODE¬O".Range("B2:B" & y).Copy MySht.[C2]", ¦ý¶K¨ìCÄæ®É, ¸ÓÄæ¦ìªº®æ½u³£·|³Q§R±¼
dSht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
MySht.AutoFilterMode = False '¸Ñ°£¿z¿ï¼Ò¦¡
End With |
|