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

[µo°Ý] ¿é¤J¦Ü¤£¦PÄæ¦ìÀx¦s®æ¨Ó­Ó§O§ä¥X¨â­Ó¤u§@ªí¹ïÀ³­È

[µo°Ý] ¿é¤J¦Ü¤£¦PÄæ¦ìÀx¦s®æ¨Ó­Ó§O§ä¥X¨â­Ó¤u§@ªí¹ïÀ³­È

¦U¦ì¤j¤j

1.Sheet2ªí©MSheet3ªí¬°¸ê®Æ¦C¦U¦³¼Æ¤dµ§¸ê®Æ.
2.Sheet1ªíA:CÄ欰Sheet2¿é¥X°Ï,¦ÓD:GÄ欰Sheet3¿é¥X°Ï.
3.¦ÛA7©MD7Àx¦s®æ©¹¤U¶}©l°µ­Ó§O¸ê®Æ¿é¤J,¨Ò¦p:
A7Àx¦s®æ

TEST25.rar (32.43 KB)

¦U¦ì¤j¤j

1.Sheet2ªí©MSheet3ªí¬°¸ê®Æ¦C¦U¦³¼Æ¤dµ§¸ê®Æ.
2.Sheet1ªíA:CÄ欰Sheet2¿é¥X°Ï,¦ÓD:GÄ欰Shee ...
luke µoªí©ó 2012-9-20 20:19

Module1
  1. Public v2, v3
½Æ»s¥N½X
ThisWorkBook
  1. Private Sub Workbook_Open()
  2.   Dim rTar As Range
  3.   
  4.   Set v2 = CreateObject("Scripting.Dictionary")
  5.   Set v3 = CreateObject("Scripting.Dictionary")
  6.   
  7.   With Sheets("sheet2")
  8.     For Each rTar In .Range(.[A2], .[A2].End(xlDown))
  9.       v2(rTar & "") = rTar.Row
  10.     Next rTar
  11.   End With
  12.   
  13.   With Sheets("sheet3")
  14.     For Each rTar In .Range(.[A2], .[A2].End(xlDown))
  15.       v3(rTar & "") = rTar.Row
  16.     Next rTar
  17.   End With
  18. End Sub
½Æ»s¥N½X
Sheet1
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.   Dim iI%
  3.   Dim rTar As Range
  4.   
  5.   Set rTar = Target
  6.   With rTar
  7.     Select Case .Column
  8.    
  9.       Case 1 ' °Ï¤À
  10.         With .Parent
  11.           For iI = 2 To 3
  12.             .Cells(rTar.Row, iI) = Sheets("sheet2").Cells(v2(rTar.Text), iI)
  13.           Next iI
  14.         End With
  15.    
  16.       Case 4 ' ²¼¸¹
  17.         With .Parent
  18.           For iI = 2 To 4
  19.             .Cells(rTar.Row, iI + 3) = Sheets("sheet3").Cells(v3(rTar.Text), iI)
  20.           Next iI
  21.         End With
  22.    
  23.     End Select
  24.   End With
  25. End Sub
½Æ»s¥N½X
TEST25-ans.zip (38.42 KB)
Àɮפ¤ Sheet1 ¥k¤U¸ê®Æ¬O«O¯d¨ÓÅçÃҥΪº.

TOP

¦^´_ 2# luhpro


    ÁÂÁÂluhpro ¦^ÂÐ

    ´ú¸Õ sheet1ªíA©MDÄæ¥ô ¤@Àx¦s®æ°µ¸ê®Æ§R°£®É·|¥X²{1004¿ù»~¦p¤U¹Ï¥Ü
    1004.jpg
   
    ¥H¤W

TOP

¦^´_ 3# luke
Sheet1 ªºµ{¦¡½X
  1. Option Explicit
  2. Private Sub Worksheet_Change(ByVal Target As Range)
  3.     Dim M As Variant
  4.     Application.EnableEvents = False
  5.     Select Case Target.Column
  6.         Case 1 To 3                             'A-CÄæÀx¦s®æ¦³¸ê®ÆÅÜ°Ê
  7.             M = Application.Match(Cells(Target.Row, 1), Sheets("sheet2").Columns(1), 0)
  8.             '¦b"sheet2"!AÄæ ¤¤´M§äsheet1!AÄæ¸ê®Æ
  9.             If IsError(M) Then                  '§ä¤£¨ì®É Match¨ç¼Æ¶Ç¦^#N/A ¿ù»~­È
  10.                 Cells(Target.Row, 2).Resize(, 2) = ""
  11.             Else                                '§ä¨ì®É Match¨ç¼Æ¶Ç¦^Àx¦s®æªº¦C¸¹
  12.                 Cells(Target.Row, 2).Resize(, 2) = Sheets("sheet2").Cells(M, 2).Resize(, 2).Value
  13.             End If
  14.         Case 4 To 7                              'D-GÄæÀx¦s®æ¦³¸ê®ÆÅÜ°Ê
  15.             M = Application.Match(Cells(Target.Row, 4), Sheets("sheet3").Columns(1), 0)
  16.             '¦b"sheet3"!AÄæ ¤¤´M§äsheet1!DÄæ¸ê®Æ
  17.             If IsError(M) Then
  18.                 Cells(Target.Row, 5).Resize(, 3) = ""
  19.             Else
  20.                 Cells(Target.Row, 5).Resize(, 3) = Sheets("sheet3").Cells(M, 2).Resize(, 3).Value
  21.             End If
  22.     End Select
  23.     Application.EnableEvents = True
  24. End Sub
½Æ»s¥N½X

TOP

¦^´_ 3# luke

¤@¯ë¼Ò²Õ
  1. Public Dic As Object
  2. Sub Auto_Open()
  3. Dim Sht(), A As Range
  4. Set Dic = CreateObject("Scripting.Dictionary")
  5. Sht = Array("Sheet2", "Sheet3")
  6. For Each sh In Sht
  7.    With Worksheets(sh)
  8.       For Each A In .Range(.[A1], .[A1].End(xlDown))
  9.          Select Case sh
  10.          Case "Sheet2"
  11.          Dic(A.Value) = Array(A.Offset(, 1).Value, A.Offset(, 2).Value)
  12.          Case "Sheet3"
  13.          Dic(A.Value) = Array(A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value)
  14.          End Select
  15.         Next
  16.     End With
  17. Next
  18. End Sub
½Æ»s¥N½X
Sheet1¼Ò²Õ
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Target.Count > 1 Then Exit Sub
  3. If IsEmpty(Dic(Target.Value)) Or Target.Count > 1 Then Exit Sub
  4. s = IIf(Target.Column = 1, 2, IIf(Target.Column = 4, 3, 0))
  5. k = UBound(Dic(Target.Value)) + 1
  6. If s <> k Then Exit Sub
  7. Application.EnableEvents = False
  8. Target.Offset(, 1).Resize(, k).Value = Dic(Target.Value)
  9. Application.EnableEvents = True
  10. End Sub
½Æ»s¥N½X
¦sÀÉ«áÃö³¬¦A­«·s¶}±ÒÀÉ®×
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¦³¤ß´N¦³ºÖ¡A¦³Ä@´N¦³¤O¡A¦Û³yºÖ¥Ð¡A¦Û±oºÖ½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD