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

[µo°Ý] ¦p¦ó¨Ì¥X³f³æªº¥X³f³æ¸¹¡A¥s¥X¤§«e¦s¦b¦b²M³æ¤¤ªº¸ê®Æ??

¦^´_ 9# §±æ¢

¦^´_ 9# §±æ¢
  1. ½Ð°Ý§Ú±Nµ{¦¡¶K¤W«á¡AÂI«ü©w¥¨¶°§ä¤£¨ì Worksheet_Change(ByVal Target As Range)
  2. ­n«ç»ò¨Ï¥Î©O?
½Æ»s¥N½X
§A¬O·Q¥Îªº¹Ï¤ù«ö¶s, «ü©w¥¨¶° µ¹ Worksheet_Chang(ByVal Target As Range)?
¦ý¬Ý¤£¨ì Worksheet_Change(ByVal Target As Range) ¥i¥H«ü©w?
¦]¬° sub xxxx(ByVal Target As Range) ¦³°Ñ¼Æ¬OµLªk«ü©w¥¨¶°ªº,

TOP

¦^´_ 12# yen956
  1. ¦]¬° sub xxxx(ByVal Target As Range) ¦³°Ñ¼Æ¬OµLªk«ü©w¥¨¶°ªº,
½Æ»s¥N½X
³o¤£¹ïªº

¸Õ¸Õ¬Ý sheet1ªº
  1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  2.     MsgBox Target.Address
  3. End Sub
½Æ»s¥N½X
¨ä¥L¼Ò²Õ¤¤
  1. Sub Ex()
  2.     Run "sheet1.Worksheet_SelectionChange", Range("A15")
  3. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 13# GBKEE

        ¥»¨Ó¦³¨Ç·§©À¤F¡A¬Ý§¹¤§«á¤S¶Ã±¼¤F:'(

    ½Ð°Ý§ÚªºG9:G24¦³³]©w¨ç¼Æ¡A°õ¦æ«á·|±N¨ç¼ÆÂл\±¼¡A¦³¸ÕµÛ±q
    Range([A9].Resize(, 8), [A9].Resize(, 8).End(xlDown)).ClearContents ¤¤ªº(,8)§ï¦¨(,6)
    ÃÒ©ú§Úªº·Qªk¦³¿ù¡K

TOP

¦^´_ 14# §±æ¢
¬O­n§ï¦¨¦p¦¹¶Ü?
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim AR(), s%, T As Integer
  3.     Application.EnableEvents = False
  4.     If Target.Address = "$H$5" Then
  5.         T = 6        '³]©wÄæ¼Æ  *********
  6.         With Sheets("¥X³f²M³æ")
  7.             For Each a In .Range(.[L3], .[L3].End(xlDown))
  8.                 If a = Target Then
  9.                     m = a.Offset(, -2).Value
  10.                     ReDim Preserve AR(s)
  11.                     AR(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, T).Value))
  12.                     'Ar(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, 8).Value))
  13.                     s = s + 1
  14.                 End If
  15.             Next
  16.         End With
  17.         Range([A9].Resize(, T), [A9].Resize(, T).End(xlDown)).ClearContents
  18.         'Range([A9].Resize(, 8), [A9].Resize(, 8).End(xlDown)).ClearContents
  19.         [C3] = m
  20.         [A9].Resize(s, T) = Application.Transpose(Application.Transpose(AR))
  21.         '[A9].Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  22.     End If
  23.     Application.EnableEvents = True
  24. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 15# GBKEE

    ¥X²{ ¤pµøµ¡
    °õ¦æ¶¥¬q¿ù»~'1004':
    µLªk§ïÅܦX¨ÖÀx¦s®æ¤¤ªº¤@³¡¥÷¡C
   
   §Ú¦b·Q¥s¦^¹L±b¥t¥~¶}·sªº¤u§@ªí¡A¦p¦¹«K¤£·|¼vÅT¥X³f³æªº¨ç¼Æ
   ¥u¬O³Ì¤U¤è°e³f¤è¦¡ªº¨º­Ó®Ø®Ø¡A¤å¦r·|Åܦ¨ªÅ¥Õªº= =
   ¤£ª¾¹D¥i¤£¥i¥HÀ°§Ú­×§ïµ{¦¡?
   ÁÂÁÂ!!

TOP

¦^´_ 13# GBKEE
§±æ¢ ªº­ì©lÀɮצ³¤£¤Ö¡i¹Ï§Î«ö¶s¡j,
³o¨Ç¡i¹Ï§Î«ö¶s¡j³£¬O§Q¥Î¡i«ü©w¥¨¶°¡j, ¥h°õ¦æ¥¨¶°,
¬G§Ú©Ò»¡ªº¡i«ü©w¥¨¶°¡j, ©M§A©Ò»¡ªº¡i«ü©w¥¨¶°¡j(Run Marco)
ªº¤º²[¬O¤£¤@¼Ëªº.
½Ð¬Ý¤U¹Ï´N©ú¥Õ¤F:

TOP

¦^´_ 16# §±æ¢

panmoongª«®ÆºÞ²z.zip (47.2 KB)

³Â·Ð§A¤F:)
ÁÂÁÂ!

TOP

¦^´_ 17# yen956


§Ú¤§«e³£¬O¥Î¥¨¶°¡A¤£©ú¥Õ Worksheet_Change  ¬°¦óµLªk«ü©w¥¨¶°
«á¨Ó¤~ª¾¹D³oºØªº¥s¨Æ¥óÅX°Êµ{¦¡
¦³­Ó·§©À¤F¡A¬Ý§A­Ìªº°Q½×¤S·w¤F
§A­Ì¤Ó¼F®`¤F

TOP

¦^´_ 17# yen956
Âà­ÓÅs¤]¥i¥H
  1. Sub Test()
  2.     Test1 Range("H5")
  3. End Sub
  4. Sub Test1(ByVal Target As Range)
  5.     MsgBox Target.Address
  6. End Sub
½Æ»s¥N½X
¦^´_ 18# §±æ¢
­×§ï¤@¤U,¸Õ¸Õ¬Ý
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Dim Ar(), s%
  3.     Application.EnableEvents = False
  4.     If Target.Address = "$H$5" Then
  5.         With Sheets("¥X³f²M³æ")
  6.             For Each a In .Range(.[L3], .[L3].End(xlDown))
  7.                 If a = Target Then
  8.                     m = a.Offset(, -2).Value
  9.                     ReDim Preserve Ar(s)
  10.                     Ar(s) = Application.Transpose(Application.Transpose(a.Offset(, -11).Resize(, 8).Value))
  11.                     s = s + 1
  12.                 End If
  13.             Next
  14.         End With
  15.         If s > 0 Then
  16.             [C3] = m
  17.             With [A9:H24]
  18.                 .Cells = ""
  19.                 .Cells(1).Resize(s, 8) = Application.Transpose(Application.Transpose(Ar))
  20.             End With
  21.         Else
  22.             MsgBox "¥X³f²M³æ ¤¤¨S¦³ " & Target
  23.         End If
  24.     End If
  25.     Application.EnableEvents = True
  26. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 20# GBKEE


    ¥i¥H¤F¡A¤Ó·PÁ§A¤F¡C

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD