yen956

¦^´_ 1# j2888237
¸Õ¸Õ¬Ý:

1. Option Explicit
   
2. '¨C·s¼W¤@±i Worksheet, ´N±N¦¹VBA½Æ»s¹L¥h
   
3. Private Sub Worksheet_Change(ByVal Target As Range)
   
4.     Dim rngD, rngA As Range, sh1 As Worksheet, endRow As Integer
   
5.     Dim ¥N½X
   
6.     Set sh1 = Sheets("¨®¤l¸ê®Æ")
   
7.    
   
8.     endRow = sh1.[A2000].End(xlUp).Row
   
9.     Set rngA = sh1.[A2].Resize(endRow, 1)
   
10.    
    
11.     endRow = [D2000].End(xlUp).Row
    
12.     Set rngD = [D2].Resize(endRow, 1)
    
13.    
    
14.     If Not Intersect(Target, rngD) Is Nothing Then
    
15.         ¥N½X = Application.Match(Target, rngA, 0)
    
16.         On Error Resume Next
    
17.         Target = sh1.[A1].Offset(¥N½X, 2)
    
18.     End If
    
19. End Sub

½Æ»s¥N½X

yen956

¦^´_ 6# j2888237
©êºp, ¨Sª`·N¨ì¤é´Á©¹¾îªº,
½Ð±N
Set rngD = [D2].Resize(endRow, 1)
§ï¦¨
Set rngD = [D2].Resize(endRow, 31)
´N¥i¥H¤F, ©êºp!!

¦pªG¤é´Á§ï¦¨±q[E2]¶}©l, «h
Set rngD = [E2].Resize(endRow, 31)
´N¥i¥H¤F

­Y§ï
Set rngE = [E2].Resize(endRow, 31)
«h³o¸Ì¤]­n§ï
If Not Intersect(Target, rngE) Is Nothing Then

yen956

¦^´_ 8# j2888237
¤j¤j§A¦n:
½Ð±N
    '¥Ñ[D2000]¶}©l¦V¤W¬d, ª½¨ì§ä¨ì«DªÅ¥Õ®æ¬°¤î
    endRow = [D2000].End(xlUp).Row
§ï¦¨(¦]¥q¾÷¦b ÄæC)
    '¥Ñ[C2000]¶}©l¦V¤W¬d, ª½¨ì§ä¨ì«DªÅ¥Õ®æ¬°¤î
    endRow = [C2000].End(xlUp).Row
¤]¥i§ï¦¨ endRow = 2000, ©T©w rngD ªº½d³ò,
¦ý½d³ò·U¤j. Excel ªº°õ¦æ®Ä²v·U®t.
¤S¤W¦¸¨S»¡²M, rngD ¥u¬O­ÓÅܼÆ(©R¦W¬° Rng, myRng, XY §¡¥i),
»P ÄæD µLÃö, »P
    Set rngD = [D2].Resize(endRow, 31)
¤~¦³Ãö.