GBKEE

¦^´_ 3# hu0318s

1. Sub ex()
   
2. Dim A As Range   'DIM («Å§i¬°¨p¥ÎÅܼÆ) A  AS(«ü©wÅܼƫ¬ºA) Range '¥i°Ñ¦ÒVBA Range ÄÝ©Ê ªº»¡©ú
   
3. With Sheet1      'With ³¯­z¦¡ ¦b¤@­Ó³æ¤@ª«¥ó©Î¤@­Ó¨Ï¥ÎªÌ¦Û­q«¬ºA¤W°õ¦æ¤@¨t¦Cªº³¯­z¦¡
   
4.     Set A = .Cells.Find("¤u¥O", lookat:=xlWhole)
   
5.     'a =>Range  .Àx¦s®æ¤¤¸Óª«¥ó¥Nªí©Ò§ä¨ìªº²Ä¤@­Ó¥]§t©Ò´M§ä¸ê°TªºÀx¦s®æ(µ¥....
   
6.     If Not A Is Nothing Then A.CurrentRegion.Copy Sheet2.[A1]
   
7.     'Not A Is Nothing :Not (ÅÞ¿è§_©w¡CªðÂà, ¤£¬O) A Is Nothing(A ¬O ªÅªºÅܼÆ) -> ¦³§ä¨ì
   
8. End With
   
9. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 7# hu0318s
½ÐªþÀÉ,¥i¤F¸Ñ§A©Ò»¡ªº°ÝÃD

GBKEE

¦^´_ 9# hu0318s

1. 'End ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¨Ó·½½d³ò¤§°Ï°ìµ²§À³BªºÀx¦s®æ¡C
   
2. 'µ¥©ó«ö END+¦V¤WÁä¡BEND+¦V¤UÁä¡BEND+¦V¥ªÁä©Î END+¦V¥kÁä¡C°ßŪ Range ª«¥ó¡C
   
3. Sub Ex2()
   
4.     Dim A As Range
   
5.     With Sheet1
   
6.         Set A = .Cells.Find("¤u¥O", lookat:=xlWhole)
   
7.         If Not A Is Nothing Then
   
8.              .Range(A, A.End(xlDown)).Copy Sheet3.[A1]
   
9.         End If
   
10.         Set A = .Cells.Find("«~¦W", lookat:=xlWhole)
    
11.         If Not A Is Nothing Then
    
12.              .Range(A, A.End(xlDown)).Copy Sheet3.[B1]
    
13.         End If
    
14.     End With
    
15. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 11# ML089

1. Option Base 1
   
2. Sub Ex()
   
3.     Dim AR(), i As Integer, A As Range
   
4.     AR = Array("¤u¥O", "«~¦W", "MRP®Æ»ô¤é")
   
5.     With Sheet1
   
6.         For i = 1 To UBound(AR)
   
7.             Set A = .Cells.Find(AR(i), lookat:=xlWhole)
   
8.             If Not A Is Nothing Then .Range(A, A.End(xlDown)).Copy Sheet3.Cells(1, i)
   
9.         Next
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 15# hu0318s

1. Option Base 1
   
2. Sub Ex()
   
3.     Dim AR(), i As Integer, A As Range
   
4.      AR = Array("¤u¥O", "«~¦W", "MRP®Æ»ô¤é")
   
5.     With Workbooks.Open(Filename:="C:\book1.xls") 'Àˬd¬Ý¬Ý C:¦³book1.xls¶Ü?
   
6.         For i = 1 To UBound(AR)
   
7.             Set A = .Worksheets("sheet1").Cells.Find(AR(i), lookat:=xlWhole)
   
8.             If Not A Is Nothing Then .Worksheets("sheet1").Range(A, A.End(xlDown)).Copy Workbooks("book2.xls").Worksheets("Sheet3").Cells(1, i)
   
9.             '¦p³oµ{¦¡½X¬OWorkbooks("book2.xls")±M®×ªºµ{¦¡½X
   
10.             'If Not A Is Nothing Then .Worksheets("sheet1").Range(A, A.End(xlDown)).Copy Sheet3.Cells(1, i)
    
11.             'Sheet3 ¬O¤u§@ªíª«¥óªº¦WºÙ µLªk¥ÎWorkbooks("book2.xls").Sheet3
    
12.             '»Ý¬OWorkbooks("book2.xls").Worksheets("¤u§@ªí¦WºÙ")
    
13.         Next
    
14.         .Close False
    
15.     End With
    
16. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 18# hu0318s

¤U¸ü (140.64 KB)

2013-12-3 15:14

GBKEE

¦^´_ 21# hu0318s
SpecialCells ¤èªk   ¶Ç¦^ Range ª«¥ó¡A¦¹ª«¥ó¥Nªí»P«ü©w«¬ºA¤Î­È¬Û²Å¦Xªº©Ò¦³Àx¦s®æ¡CRange ª«¥ó¡C
EntireColumn ÄÝ©Ê ¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí¥]§t¸Ó«ü©w½d³òªº¾ã­ÓÄæ (©Î­Y¤zÄæ)¡C
Areas ÄÝ©Ê         ¶Ç¦^ Areas ¶°¦X¡A¦¹¶°¦X¥Nªí¦h­«½d³ò¤¤ªº©Ò¦³½d³ò¡C

1. Option Base 1
   
2. Sub Ex2()
   
3.     Dim ar(), i As Integer, a As Range
   
4.     Sheet2.[a:x].Clear
   
5.     ar = Array("T1", "T2", "t3")
   
6.     With Workbooks.Open(Filename:="C:\123.xls")
   
7.         For i = 1 To UBound(ar)
   
8.             Set a = .Worksheets("data").Cells.Find(ar(i), lookat:=xlWhole)
   
9.             If Not a Is Nothing Then a.EntireColumn.SpecialCells(xlCellTypeConstants).Copy Worksheets(2).Cells(1, i)
   
10.         Next
    
11.        .Close False
    
12.     End With
    
13. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 23# hu0318s
¤£¥²±Ëªñ¨D»·

¤U¸ü (116.5 KB)

2013-12-8 12:57