[µo°Ý] ¦p¦ó§Q¥ÎVBA¦^¶Ç¤å¦r?

leehom

°²¦pA1,B1

¦p¦ó¦bB1¿é¤J¥ô¦ó¤å¦r«á,A1·|¥X²{¯S©w¦w±Æªº¤å¦r,¦ýA1¤£·Q¿é¤J¥ô¦ó¨ç¼Æ,­n«O«ùªÅ¥Õ¡C
¨Ò¦p: B1¦p¿é¤JABC,A1·|¦Û°Ê²K¥[¹w³]ªº¤å¦r¦p¡¨DEF¡¨,¦ýA1¥»¨­¬OªÅ®æªº,¨ºÀ³¸Ó­n«ç»ò°µ©O?¨D°ª¤â«ü±Ð!

lpk187

¦^´_ 1# leehom


    ¤£ª¾¹D§A©Ò¿×ªº¯S©w¤å¦r¬O¤°»ò¡H
¤£¹L§A¥i¥H§Q¥Î¤u§@ªí¨Æ¥ó¨Ó»s§@

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$B$1" Then
   
3.     If Target = "" Then
   
4.         Target.Offset(, -1) = ""
   
5.     Else
   
6.         Target.Offset(, -1) = Target & " DEF"
   
7.     End If
   
8. End If
   
9. End Sub

½Æ»s¥N½X

leehom

¦^´_ 2# lpk187


ªGµM¦³¥Î°Ú,ÁÂÁ°Ú!!ÁÙ·Q¦A½Ð±Ð¤@¤U,¦pªG·Q¦^¶Çªº¬O¼Æ¦r,¦p¦ó³]©w»¼¼W?
¨Ò¦pB1¿é¤J¤å¦r,A1¦^¶Ç¬O"1",B2¿é¤J¤å¦r,A2¦^¶Ç¬O"2",B3¿é¤J¤å¦r,A3¦^¶Ç¬O"3",
¦p¦¹Ãþ±À,­n¦p¦ó½s¼g©O?ÁÂÁ®v¥S«ü±Ð!!!:D

lpk187

¦^´_ 3# leehom


    ¨Ì·Ó§A¶Ç¦^¼Æ¦r¡A¥i¥H§Q¥ÎTargetªº¦C¸¹(Row)¦^¶Ç
IJµo«h§ï¬°²Ä¤GÄæColumn = 2 ¡A¤]´N¬OBÄæ

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. 
   
3. If Target.Column = 2 Then
   
4.     If Target = "" Then
   
5.         Target.Offset(, -1) = ""
   
6.     Else
   
7.         Target.Offset(, -1) = Target & " " & Target.Row
   
8.     End If
   
9. End If
   
10. End Sub

½Æ»s¥N½X

leehom

¦^´_ 4# lpk187

ªº½T«Ü¯«©_,§Úµo²{¿é¤J¤å¦r¬O¦¨¥\ªº,¦ý¤U©Ô»¼¼W´N¤£¦æ¤F,¬°¬Æ»ò·|³o¼Ë©O®v¥S?

GBKEE

¦ý¤U©Ô»¼¼W´N¤£¦æ¤F,¬°¬Æ»ò·|³o¼Ë©O®v¥S?
leehom µoªí©ó 2015/9/15 23:39

³o¼Ë´N¥i¥H.

1. Option Explicit
   
2. Private Sub Worksheet_Change(ByVal Target As Range)
   
3.     Dim E As Range
   
4.     If Target.Column = 2 Then
   
5.         For Each E In Target
   
6.             If E = "" Then
   
7.                 E.Offset(, -1) = ""
   
8.             Else
   
9.                 E.Offset(, -1) = E & " " & E.Row
   
10.             End If
    
11.         Next
    
12.     End If
    
13. End Sub

½Æ»s¥N½X

leehom

Workbook.zip (27.66 KB)

¤å¥ó
¤U¸ü¦¸¼Æ: 7

2015-9-16 13:10

¦^´_ 6# GBKEE




·PÁ¶W¯Åª©¥Dªº¦^ÂаڡA§Ú¸Õ¹L¬O¦³®Äªº¡A¦ý§Ú·Q±N¥N½X¤Þ¤J³o­Ó¤u§@ªí«oµLªk¦¨¥\¡A
§Ú¤§«e½Ð±Ð¤F¨ä¥L°ª¤H¡A¼g¤F¤@¨ÇVBA¥N½X¡A¤@¶}±Òªº¸Ü·|¼u¥Xµøµ¡­n¨D¿é¤J¸¹½X¡A
¤å¦r¼Ò¦¡¬OABCD0123456¡AµM«á¿é¤J¼W¥[¦h¤Ö­Ó¡A¥¦·|¦Û°Ê»¼¼W¡A¹³¹Ï¤W¨º¼Ë¡A
§Úªº­ì·N¬O§Æ±æ¬OAÄæ"QTY"·|¦Û°Ê²K¥[¸¹½XµM«á¶¶À³»¼¼W¡A¦ý«oµLªk¦¨¥\¡A
¥u¦n¦A½Ð¨D¦U¦ì°ª¤â¤F¡AÁÂÁ°ڡI¡I

lpk187

¦^´_ 7# leehom


    ¾ã­Ó¨«¦V¿ù»~¤F¡A¤£À³¸Ó³o¼Ë¥h¼g
¤@­Ó°ÝÃD¬O­n¦Ò¼{«á¨Ó¨Ï¥ÎªÌ¸Ó¦p¦ó¿é¤J¡A§A²Ä¤@­Ó°ÝÃD¨ì«á­±³o¤@­Ó°ÝÃD¡A®¦¦Òªº¤è¦V§¹¥þ¤£¦P¤F¡C

1. Sub worksheet_change(ByVal Target As Range)
   
2.      If Target.Address = "$B$2" Then
   
3.      Application.EnableEvents = False
   
4.      i = Application.InputBox("How many numbers you want to add?", "Prompt", , , , , , 1) - 1
   
5.      For j = Target.Row + 1 To Target.Row + i
   
6.          Cells(j, Target.Column) = Left(Cells(j - 1, Target.Column), 5) & Right(Cells(j - 1, Target.Column), 7) + 1
   
7.          Cells(j, Target.Column - 1) = Cells(j, Target.Column - 1).Row
   
8.      Next
   
9.      Application.EnableEvents = True
   
10.      End If
    
11. End Sub

½Æ»s¥N½X

leehom

¦^´_ 8# lpk187




¥ýÁ¤F,¥u®t¤@ÂIÂI´N§¹¬ü¤F,¦]¬°²{¦bA3¬O3,A4¬O4,¦ýªí®æ¤WÀ³¸Ó¬OA2¬O1,A3¬O2,A4¬O3,¦A¦¸«ô¦«²³¤j¯«¤F¡C

lpk187

¦^´_ 9# leehom

1. Sub worksheet_change(ByVal Target As Range)
   
2.      If Target.Address = "$B$2" Then
   
3.      Application.EnableEvents = False
   
4.         Cells(2, Target.Column - 1) = 1
   
5.      i = Application.InputBox("How many numbers you want to add?", "Prompt", , , , , , 1) - 1
   
6.      For j = Target.Row + 1 To Target.Row + i
   
7.          Cells(j, Target.Column) = Left(Cells(j - 1, Target.Column), 5) & Right(Cells(j - 1, Target.Column), 7) + 1
   
8.          Cells(j, Target.Column - 1) = Cells(j, Target.Column - 1).Row - 1
   
9.      Next
   
10.      Application.EnableEvents = True
    
11.      End If
    
12. End Sub

½Æ»s¥N½X