198188

¤U¸ü (83.95 KB)

2012-12-8 00:57

¤U¸ü (84.9 KB)

2012-12-8 00:57

¤U¸ü (89.76 KB)

2012-12-8 00:57

½Ð°Ý­þ¸Ì¥X¿ù¤F¡HRng(2).copy Rng(1)

GBKEE

¦^´_ 40# 198188
With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
±N¦¹ÀÉ®×»P¥¼¥X¿ùÀÉ®×¹ï½Õ¬Ý¬O§_¤@¼Ëªº¥X¿ù,¥i¨Ì¤U¤èµ{§Ç¬Ý¬Ý

¦^´_ 41# 198188

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng(1 To 2) As Range
   
4.     With Workbooks("payment.XLSM").Sheets("2012")
   
5.         Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m
   
6.         With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")
   
7.             Set Rng(2) = .[A2:L2]
   
8.             Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
   
9.             MsgBox .Rows.Count - Rng(2).Rows.Count < .Rows.Count - Rng(1).Row 'True: Rng(2)½d³ò¤j©ó Rng(1) ´N¦³¿ù»~
   
10.             Rng(2).Copy Rng(1)
    
11.             .Parent.Close False
    
12.         End With
    
13.     End With
    
14. End Sub

½Æ»s¥N½X

198188

¦^´_ 42# GBKEE


    01.Option Explicit

02.Sub Ex()

03.    Dim Rng(1 To 2) As Range

04.    With Workbooks("payment.XLSM").Sheets("2012")

05.        Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m

06.        With Workbooks.Open("C:\Users\patrick.HKG\Desktop\Patrick.XLSX").Sheets("SHEET1")

07.            Set Rng(2) = .[A2:L2]

08.            Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))

09.            MsgBox .Rows.Count - Rng(2).Rows.Count < .Rows.Count - Rng(1).Row 'True: Rng(2)½d³ò¤j©ó Rng(1) ´N¦³¿ù»~

10.            Rng(2).Copy Rng(1)

11.            .Parent.Close False

12.        End With

13.    End With

14.End Sub
¬ORng(2)½d³ò¤j©ó Rng(1)¡A¦ý¬OSet Rng(1) = .[E1000].End(xlUp).Offset(, -4) '³o Rng(1)ªº¦ì¸m ¡]³o¥yªº·N«ä¬O¤£¬Oµ¥©ó¥ÑE1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C¡AOFFSET( , -4)±NEÄæ§ï¦¨AÄæ
Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))¬O§_·N«ä±qAÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§¡H

GBKEE

¦^´_ 43# 198188
Set Rng(1) = .[E1000].End(xlUp):  E1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C=> ¦p¬O E999
Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) => A999
¨º A999 ¨ì Àɮש³³¡ ªº¦C¼Æ¬O 2003-> 65536-999 +1
*********
Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ ?? ¦p¤j©ó 65536-999 +1
********** ½Æ»sªº¸ê®Æ½d³ò>¶K¤W¦ì¸mªº½d³ò ?? ¨º¦h¥Xªº¸ê®Æ­nÂ\­þ¸Ì ??****

198188

¦^´_ 44# GBKEE


    Set Rng(1) = .[E1000].End(xlUp):  E1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C=> ¦p¬O E999
¨º»ò½Ð°Ý§ÚÀ³¸Ó¦p¦ó¼g³o¥y¡A±N¥¦¼g¦¨set rng(1) = ·í«eworksheet(2012)E Ä檺³Ì«á¤@¦C¥[1¡H
¨ä¹ê§ÚAÄæ¤j³¡¤À³£¨S¦³¸ê®Æ

198188

¦^´_ 44# GBKEE


    Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ ?? ¦p¤j©ó 65536-999 +1
********** ½Æ»sªº¸ê®Æ½d³ò>¶K¤W¦ì¸mªº½d³ò ?? ¨º¦h¥Xªº¸ê®Æ­nÂ\­þ¸Ì ??****
©ÎªÌ
³o¥y¥i§_§ï¦¨AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ,¦ý°ò©ó¦³®É·|¹j¶}¤@¦C¡A¥i§_¥[¦h¥y§ä¨ì³Ì«á¤@µ§ªº¨º¦C«á¥[¨â¦C¡A¦pªG¤]¨S¦³¸ê®Æ¡A¤~½T»{¬O³Ì«á¤@µ§¡A§_«hÄ~Äò©¹¤U¶}©l

GBKEE

¦^´_ 46# 198188
41# ªº¿ù»~¦b

1.           Set Rng(2) = .[A2:L2]
   
2.           Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))   'AÄæ¨S¸ê®Æ [A2].End(xlDown) ·|¨ìÀɮש³³¡

½Æ»s¥N½X

39# ¤w´£¿ô§A: µ¹§Aªºµ{¦¡½X­n®ø¤Æ¤@¤U,VBA¤~·|¶i¨B

1.    
   
2.           Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò
   
3.          '
   
4.          '
   
5.            Set Rng(2) = .[A2:L2]
   
6.           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î
   
7.             Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)  
   
8.            '[E1].End(xlDown) ¨ìEÄ榳¸ê®Æªº¦a¤è·|°±¤î,¤~¤£·|¨ìÀɮש³³¡
   
9.             Rng(2).copy Rng(1).Cells(1, -3)

½Æ»s¥N½X

198188

¦^´_ 47# GBKEE


   ·P¿E¸ÑÄÀ¡A
¦ý¬O§Ú¸Õ¹L°£¤F­è¤~³o¥y Set Rng(1) = Rng(1).End(xlDown).Offset(1)¥X²{°ÝÃD¥~¡A·í§ÚEÄ椤¶¡¦³¤@¦CªÅ¥Õ¡A´N¤£À´©¹¤Ucopy¡A©Ò¥H¤~¥ÎAÄæ¡A©ú¥Õ­ì²z¡A¦ý´N¬OÂण¹L¨Ó«ç¼Ë§ï¡H

         With Workbooks.Open("C:\Documents and Settings\USER\®à­±\Patrick.XLSX").Sheets("SHEET1")

            Set Rng(2) = .[A2:L2]

            Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)

            Rng(2).Copy Rng(1).Cells(1, -3)

            .Parent.Close False

198188

¦^´_ 47# GBKEE


    ¥¿¦p²Ä38¶KPatrick.XLSX¡AE Äæ¥u¦³´XÄæ¸ê®Æ«ç·|¶W¥X½d³ò¡HSet Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)³o¥y¥H¤°»ò§@¬°³W«h¡H

GBKEE

¦^´_ 48# 198188
·í§ÚEÄ椤¶¡¦³¤@¦CªÅ¥Õ: ¥i¥ÑÀɮש³³¡©¹¤W

1.     Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
   
2.     Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

½Æ»s¥N½X

¦^´_ 49# 198188
VBA ªº»¡©ú

1. Resize ÄÝ©Ê
   
2. ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w½Õ¾ã«ü©wªº½d³ò¡C¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò¡C
   
3. expression.Resize(RowSize, ColumnSize)
   
4. expression     ¥²¿ï¡C¸Ó¹Bºâ¦¡¶Ç¦^­n½Õ¾ã¤j¤pªº Range ª«¥ó¡C
   
5. RowSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªº¦C¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªº¦C¼Æ«O«ù¤£ÅÜ¡C
   
6. ColumnSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªºÄæ¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªºÄæ¼Æ«O«ù¤£ÅÜ¡C

½Æ»s¥N½X

1. For xi = 1 To 5
   
2.         Set Rng(2) = Rng(2).Resize(xi)
   
3.         MsgBox Rng(2).Address
   
4.     Next

½Æ»s¥N½X