Hsieh

¦^´_ 1# freeffly

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$C$1" Then
   
3. Application.EnableEvents = False
   
4. For Each a In Range("A2", [A2].End(xlDown))
   
5.   sh = Format(a, "emm")
   
6.   With Sheets(sh)
   
7.      Set c = .Rows(1).Find([C1], lookat:=xlWhole)
   
8.      r = Application.Match(a, .[A:A], 0)
   
9.      If IsNumeric(r) And Not c Is Nothing Then a.Offset(, 2) = .Cells(r, c.Column) Else a.Offset(, 2) = ""
   
10.   End With
    
11. Next
    
12. End If
    
13. Application.EnableEvents = True
    
14. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 8# freeffly

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. If Target.Address = "$C$1" Then
   
3. Application.EnableEvents = False
   
4. Range("A1").CurrentRegion.Offset(1, 2) = ""
   
5. For Each a In Range("A2", [A2].End(xlDown))
   
6.   sh = Format(a, "emm")
   
7.   For Each sht In Sheets
   
8.   If sht.Name = sh Then
   
9.   With sht
   
10.      Set c = .Rows(1).Find([C1], lookat:=xlWhole)
    
11.      r = Application.Match(a, .[A:A], 0)
    
12.      If IsNumeric(r) And Not c Is Nothing Then a.Offset(, 2).Resize(, 2).Value = .Cells(r, c.Column).Resize(, 2).Value
    
13.   End With
    
14.   End If
    
15.   Next
    
16. Next
    
17. End If
    
18. Application.EnableEvents = True
    
19. End Sub

½Æ»s¥N½X