[µo°Ý] ¦p¦ó¤ñ¹ï¸ê®Æ«á±N¤£¦P¤§¼Æ¾Ú©ó·s¼Wsheet¤W

cmo140497

Dear ¦U¦ì¹F¤H :
    ¤p§Ì¦³¤G­Ósheetªº¸ê®Æ,¤¬¬Û¤ñ¹ï«á,±N¬Û¦Pªººû«ù­ì­È,¤£¬Û¦Pªº«h¥H"0"¨ú¥N,µ{¦¡¦³ÂI°ÝÃD,·Ð½Ð¦U¦ì¹F¤HÀ°¦£¸Ñ¨M¤p§ÌªººÃ´b,·P®¦!

1. Option Explicit
   
2. Sub test1()
   
3.     '´ú¸Õ¸ê®ÆE_Data01 / E_Data02¤u§@ªí
   
4.     Dim myRng1 As Range
   
5.     Dim myRng2 As Range
   
6.     Dim myCell1 As Range
   
7.     Dim myCell2 As Range
   
8.     Dim mySht  As Worksheet
   
9.     Dim i      As Long
   
10.     With Worksheets
    
11.         '«ü©w§ì¨ú¨Ó·½½d³ò»PÁä­ÈÄæ
    
12.         Set myRng1 = .Item("E_Data01").Range("A1").CurrentRegion.Columns(1)
    
13.         Set myRng2 = .Item("E_Data02").Range("A1").CurrentRegion.Columns(1)
    
14.         Set mySht = .Add(After:=.Item(.Count))  '¤u§@ªíªº·s¼W
    
15.     End With
    
16.     i = 0
    
17.    
    
18.     For Each myCell1 In myRng1.Cells
    
19.         For Each myCell2 In myRng2.Cells
    
20.             If myCell1.Value = myCell2.Value Then
    
21.                 i = i + 1
    
22.                         myCell1.EntireRow.Copy mySht.Cells(i, 1)
    
23.                 Else
    
24.                         myCell2.EntireRow.Copy mySht.Cells(i, 1)
    
25.             End If
    
26.         Next
    
27.     Next
    
28.     Set myRng1 = Nothing                'ª«¥óªºÄÀ©ñ
    
29.     Set myRng2 = Nothing
    
30.     Set myCell1 = Nothing
    
31.     Set myCell2 = Nothing
    
32.     Set mySht = Nothing
    
33. End Sub

½Æ»s¥N½X

sheet¬Û¦P¤º®e¦C©ó·s¼Wsheet¤W_20121108.zip (17 KB)

¤U¸ü¦¸¼Æ: 16

2012-11-8 10:28

Hsieh

¦^´_ 1# cmo140497

1. Sub ex()
   
2. Dim ar(), A As Range
   
3. With Sheet1
   
4.   ar = .UsedRange.Value
   
5.   For Each A In .UsedRange
   
6.      ar(A.Row, A.Column) = IIf(A = Sheet2.Range(A.Address), A, 0)
   
7.   Next
   
8. End With
   
9. With Sheets.Add(after:=Sheets(Sheets.Count))
   
10. .[A1].Resize(UBound(ar, 1), UBound(ar, 2)) = ar
    
11. End With
    
12. End Sub

½Æ»s¥N½X

cmo140497

¦^´_ 2# Hsieh


    ·PÁª©¥D¶È¥Î³o»òµuªºµ{¦¡§Y¸Ñ¨M¤F¤p§Ìªº°ÝÃD,¥tµ{¦¡¤¤¦³¤@¦æ,¤p§Ì¤£¬O«Ü¤F¸Ñ,Ãö©ó.[A1].Resize(UBound(ar, 1), UBound(ar, 2)) = ar

'Resize(Rowsize,columnsize)½Õ¾ã«ü©wªº½d³ò,¶Ç¦^Rangeª«¥ó,¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò
'UBound(Arrayname,dimension),¶Ç¦^Long­È,ªí¥Ü«ü©w°}¦C¬Yºû³Ì¤j¥i¨Ï¥Îªº°}¦C¯Á¤Þ

¬°¤°»òResize:Rowsize´N³]©w¬°1ºû,¦ÓColumnsize´N¥²¶·³]©w¬°2ºû©O?

Hsieh

¦^´_ 3# cmo140497
ar¬O±N½d³ò«ü©w¦¨ªº°}¦C¡A©Ò¥H¬O¤Gºû°}¦C¡A¦Uºûªº¤U¼Ð¬°1
Ubound(ar,1)«üªº¬Oar²Ä¤@ºûªº¤Wöt
Ubound(ar,2)«üªº¬Oar²Ä¤Gºûªº¤Wöt

198188

½Ð°Ý¦pªG·Q¹ï¤ñªº¬O¨â­Ó¤£¦Pªºexcel©O¡H«ç¼Ë°µ¡H