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

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

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

°²¦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°ª¤â«ü±Ð!

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

TOP

¥»©«³Ì«á¥Ñ leehom ©ó 2015-9-15 21:48 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ lpk187 ©ó 2015-9-15 22:34 ½s¿è

¦^´_ 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. If Target.Column = 2 Then
  3.     If Target = "" Then
  4.         Target.Offset(, -1) = ""
  5.     Else
  6.         Target.Offset(, -1) = Target & " " & Target.Row
  7.     End If
  8. End If
  9. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# lpk187

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

TOP

¦ý¤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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ leehom ©ó 2015-9-16 13:11 ½s¿è

Workbook.zip (27.66 KB)


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

TOP

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

TOP

¦^´_ 8# lpk187




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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD