ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°ª¤HÀ°¦£°£¿ù¡AÁÂÁÂ~



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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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Â\­þ¸Ì ??****

TOP

¦^´_ 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¦³¸ê®Æ

TOP

¦^´_ 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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-12-8 10:45 ½s¿è

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¨C¤ÑµL©Ò¨Æ¨Æ¡A¬O¤H¥Íªº®ø¶OªÌ¡A¿n·¥¡B¦³¥Î¤~¬O¤H¥Íªº³Ð³yªÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD