hugh0620

¦^´_  Hsieh


    ¥J²Ó¬Ý¹L«áªO¥Dªº¤è¦¡»P¥t¥~¤@¦ì¤j¤j¤è¦¡¤£¦P(³B²z¤è¦¡)
    ¦pªG»¡·Q­n¦V¥t¤@¦ì¤j ...
freeffly µoªí©ó 2012-2-23 09:09

    ¼Ó¥D§A¥ÎªºSHEET.Name¬O¥Á°ê+¦~~
    ©Ò¥HH¤j¤j¥Îsh = Format(a, "emm")ªº¤è¦¡~ ¨Ó§PÂ_§AªºSHEET¦WºÙ
    µM«á¦A¶i¥h¨º­ÓSHEET¤¤¼´¨ú¸ê®Æ

GBKEE

¦^´_ 10# hugh0620

1. r = Application.Match(a, .[A:A], 0)
   
2.      If IsNumeric(r) And Not c Is Nothing Then a.Offset(, 2) = .Cells(r, c.Column) Else a.Offset(, 2) = ""

½Æ»s¥N½X

Match¨ç¼Æ :§ä¨ì®É¶Ç¦^¼Æ¦r, §ä¤£¨ì®É¶Ç¦^ "#N/A" (¿ù»~­È)
§ï¦¨ If   Not IsError(r)  And Not c Is Nothing Then  ´N¥i¥H°£¿ù

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

freeffly

¦^´_ 10# hugh0620


    ³o­Ó§Ú¦³µo²{
   §A­Ì­Ç¦Uªº³B²z¤è¦¡¤£¤@¼Ë
   ³£¬O¾Ç²ßªº¤è¦¡

freeffly

¦^´_ 13# Hsieh


    ÁÂÁª©¥D
   ¦³·Q¨ì¥Îresize¥i¬O¨S¸Õ¥X¨Ó
   ­ì¨Ó¬O®t¦b.value