[µo°Ý] ½Ð°ÝVBA¦³¿ìªk¹F¦¨ ¿é¤J¼Æ¶q ´N¥´´X­Ó¼Æ¶qªº¤å¦r¥X¨Ó¶Ü?

starry1314

¦p

¤U¸ü (13.79 KB)

2015-5-16 17:49

lpk187

¦^´_ 5# starry1314


    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [B3].Address Then'³o¥yTarget.Address¬O«üIJ°ÊªºÀx¦s®æ¦ì§}¡A¦pªG¬O [B3].Address¡A³o­ÓÀx¦s®æªº¦ì§}¦pªG¬OTargetªº¦ì§}´N°õ¦æ If ¦¨¥ß¤Uªºµ{¦¡½X,©Ò¥H³o¸ÌªºB3¬O¥i¥HÅܧóªº
¦ý³o¸Ì¤£¯à¬O If Target = [B3] Then²²¤±¼Address«h¬OŪ¨ì"­È"(.Value)¡A°£«D§A¥Î¨ìªº¬O­È(.Value)ªº³¡¥÷
¦]¬°Target¤w¸g¬O³]©w¬O¤@­ÓRangeª«¥ó¡A«h¤]¥i¥H§Q¥ÎRangeªºÄÝ©Ê
¨Ò¦p¥i¥HŪ¨ú"¦æ"Target.Row
Ū¨ú¦CTarget.Column
Ū¨ú­ÈTarget.Value©ÎTargetªí¥Ü

starry1314

¦^´_ 4# lpk187

¦nªF¦è~~¦¬¤U¤F¡I¡I¡I

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = [B3].Address Then

    ¦³ÂI½ÆÂø...¬d¤F¤U ®e©ö²z¸Ñ¦h¤F....·Q°Ý¤U[target],¥iÀH§Ú§ó§ï¦p AA? ab?

Private¬O¡§¨p¦³¡¨¡A³o¼Ëªºµ{§Ç¥u¯à¦b«ü©w½d³ò¨Ï¥Î¡A¨Ò¦p¦bSheet¸Ì
Sub¬O¡§¤À¤ä¡¨¡A¤]´N¬O»¡¬O½Ñ¦hµ{§Ç¤§¤@
By¬O¡§¦ñÀH¡¨
val¬O¡§­È¡¨
target¬OÅܶqªº¦WºÙ¡A¥i¥HÀH«K§ï¦¨¨ä¥¦ªº¡A¨Ò¦p¡§XX¡¨
As¬O¡§§@¬°¡¨
Range¬O¡§°Ï°ì¡¨¡A³o¸Ì¬OÅܶqªº¤@ºØÃþ«¬

¤@¥¹·s¿ï¾Ü¤F¤@­Ó³æ¤¸®æ°Ï°ì¡A´N¤Þµo³o­Óµ{§Ç¡A¦P®É²£¥Í¤@­Ó¦W¦r¬°targetªºÅܶq¡A³o­ÓÅܶq¥Nªí©Ò¿ï¤¤ªº³æ¤¸®æ°Ï°ì¡C

lpk187

¦^´_ 1# starry1314

­Õ­Y¥Î¤u§@ªí¨Æ¥ó«h¥i¥H§â¤U­±µ{§Ç¶K¨ì¤u§@ªíªºµ{§Ç¤¤¡A¤£¥Î«ö«ö¶s´N¥i¹F¦¨¦Û°Ê¤Æ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = [B3].Address Then
   
3.     If Target = "" Then
   
4.         MsgBox "½Ð¿é¤J¼Æ¶q"
   
5.         Exit Sub
   
6.     End If
   
7.     Application.EnableEvents = False
   
8.     If [A7] <> "" Then
   
9.         Range("A7:" & Cells(Rows.Count, 1).End(xlUp).Address) = ""
   
10.     End If
    
11.     For I = 1 To Target
    
12.         Cells(I + 6, 1) = Target.Offset(0, 1)
    
13.     Next
    
14.     Application.EnableEvents = True
    
15. End If
    
16. End Sub

½Æ»s¥N½X

starry1314

¦^´_ 2# lpk187


    ¬Oªº¡ã¯u¬O¤Ó±j¤F¡I³o¦¸ªºµ{¦¡½X¹ï§Ú¨Ó»¡®e©ö§l¦¬¦h¤F.......

lpk187

¦^´_ 1# starry1314


    ¬O³o¼Ë¶Ü¡H

1. Sub ¥¨¶°1()
   
2. Rng = [B3]
   
3. If Rng = "" Then
   
4.     MsgBox "½Ð¿é¤J¼Æ¶q"
   
5.     Exit Sub
   
6. End If
   
7. For I = 1 To Rng
   
8.     Cells(I + 6, 1) = [C3]
   
9. Next
   
10. End Sub

½Æ»s¥N½X