[µo°Ý] 2 ±i sheet ¹ï·Ó¡AµM«á§âº|¤Fªº ¥[¤J¡Avba À³«ç¼Ë¼g¡H

119933775544

Account.zip (106.7 KB)

¤U¸ü¦¸¼Æ: 26

2010-10-19 12:00

¯uªº«Ü­W´o¡A½Ð¦U¦ì½ç±Ð¡C ¨£ªþ¥ó¡AÁ¡C

GBKEE

¦^´_ 3# basarasy
¼gªk¤£¤î¤@­Ó

1. Sub Ex()
   
2.     Dim D As Object, E, Rng As Range
   
3.     Set D = CreateObject("SCRIPTING.DICTIONARY")
   
4.     For Each E In Sheet2.Range("A2", Sheet2.Range("A" & Rows.Count).End(xlUp))
   
5.         D(E.Value) = E & ""
   
6.     Next
   
7.     For Each E In Sheet1.Range("A3", Sheet1.Range("A" & Rows.Count).End(xlUp))
   
8.         If D.Exists(E.Value) = False Then
   
9.             If Rng Is Nothing Then Set Rng = E.Resize(, 3) Else Set Rng = Union(Rng, E.Resize(, 3))
   
10.         End If
    
11.     Next
    
12.     If Not Rng Is Nothing Then Rng.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
    
13. End Sub
    
14. Sub Ex1()
    
15.     Dim E, a&, Rng As Range
    
16.     For Each E In Sheet1.Range("A3", Sheet1.Range("A" & Rows.Count).End(xlUp))
    
17.         If IsError(Application.Match(E, Sheet2.[a:a], 0)) Then
    
18.             If Rng Is Nothing Then Set Rng = E.Resize(, 3) Else Set Rng = Union(Rng, E.Resize(, 3))
    
19.         End If
    
20.     Next
    
21.     If Not Rng Is Nothing Then Rng.Copy Sheet2.Range("A" & Rows.Count).End(xlUp).Offset(1)
    
22. End Sub

½Æ»s¥N½X

119933775544

¦^´_ 3# basarasy

ÁÂÁ¦U¦ì®v¥S¡A§Ú·|¸Õ¸Õ¬Ý¡C

basarasy

¤£¥i¥Hª½±µcopy¨ìsheet2¶Ü?

oobird

1. Sub yy()
   
2.     With Sheet1
   
3.         rng = .Range(.Cells(3, 1), .Cells(3, 3).End(4))
   
4.     End With
   
5.     With Sheet2
   
6.         For i = 1 To UBound(rng)
   
7.             If .[A:A].Find(rng(i, 1), , , 1) Is Nothing Then
   
8.                 .Cells(.[a65536].End(3).Row + 1, 1).Resize(, 3) = Array(rng(i, 1), rng(i, 2), rng(i, 3))
   
9.             End If
   
10.         Next
    
11.     End With
    
12. End Sub

½Æ»s¥N½X