[µo°Ý] §ó·s¸ê®Æ¦ý¦C¦ì¤£¦P

aok669

¦U¦ì«e½ú¦n

±qBÄæ¶}©l¬O´Á¼Æ

±N''IN''¤u§@ªíªº¸ê®Æ,¶×¤J¦Ü''¥»''¤u§@ªí

¨Ò¦p:099114´Á¦Ü099116´Á¥¼§ó·s¸ê®Æ,½Ð§ì¨ú ''in''¤u§@ªí¸ê®Æ §ó·s

·PÁÂ

¸ÔÀÉ®×

updata.rar (23.48 KB)

½Ð¬Ý
¤U¸ü¦¸¼Æ: 20

2010-6-12 03:29

Hsieh

¦^´_ 4# GBKEE

1. Sub Ex()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With Sheet1
   
4.   For Each a In .Range(.[B1], .[B1].End(xlToRight))
   
5.      For Each b In .Range(.[A2], .[A2].End(xlDown))
   
6.         d(a & b) = .Cells(b.Row, a.Column).Value
   
7.      Next
   
8.   Next
   
9. End With
   
10. With Sheet2
    
11.   For Each a In .Range(.[B1], .[B1].End(xlToRight))
    
12.      For Each b In .Range(.[A2], .[A2].End(xlDown))
    
13.        If .Cells(b.Row, a.Column) = "" Then .Cells(b.Row, a.Column).Value = d(a & b)
    
14.      Next
    
15.   Next
    
16. End With
    
17. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 3# Hsieh
¼Ó¥DªºÀÉ®× "¥»"¤u§@ªí¤¤¦³¤@¨ÇÄæ¦ì¬O "IN"¤u§@ªí©Ò¨S¦³ªº ª½±µ½Æ»s¾ã­Ó¤u§@ªí·|Âл\±¼ "¥»" ­ì¦³ªº¸ê®Æ

Hsieh

³o¤£¬OINªíª½±µ½Æ»s¨ì¥»ªí´N¦n¤F¶Ü

GBKEE

¦^´_ 1# aok669

1. Sub Ex()
   
2. Dim d As Object, Rng As Range, R As Range, C As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. With Sheets("in")
   
5.     Set Rng = .Range("B2", .Cells(1, Columns.Count).End(xlToLeft).Offset(1)).Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1, .Cells(1, Columns.Count).End(xlToLeft).Column - 1)
   
6.     For Each R In Rng.Rows
   
7.         For Each C In R.Cells
   
8.             d(.Cells(C.Row, 1) & .Cells(1, C.Column)) = C
   
9.         Next
   
10.     Next
    
11. End With
    
12. With Sheets("¥»")
    
13.     Set Rng = .Range("B2", .Cells(1, Columns.Count).End(xlToLeft).Offset(1)).Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1, .Cells(1, Columns.Count).End(xlToLeft).Column - 1)
    
14.     For Each R In Rng.Rows
    
15.         For Each C In R.Cells
    
16.             If d.EXISTS(.Cells(C.Row, 1) & .Cells(1, C.Column)) Then C = d(.Cells(C.Row, 1) & .Cells(1, C.Column))
    
17.             
    
18.         Next
    
19.     Next
    
20. End With
    
21. End Sub

½Æ»s¥N½X