[µo°Ý] ¦p¦ó±N"±ÄÁʳæ"¤º®e¨Ì§Ç¼g¤J¥t¤@­Ósheet?

guaga

·Q½Ð°Ý¤@¤U¦U¦ìVBA°ª¤â~
³Ìªñ¦b¬ã¨s¤@­Ó¯àÅý
¦p¦ó±N"±ÄÁʳæ"¤º®e¨Ì§Ç¼g¤J¥t¤@­Ó"®w¦s°O¿ý"sheet?
(¥»¨Ó·Q­n¥Îªþ¥[ÀÉ®× ·|¤ñ¸û¦n»¡©ú ¥i¬Oªþ¥[¤£¤W¨Ó)

¦³ÂI¹³
https://www.youtube.com/watch?v=deRlUjhIHOo   
³oºØª¬ªp

¥H¤U¬O¼gªº¸ê®Æ,¤w¸g·Q¤F¦n¤[ª¦¤Fº¡¦hªº¤å °£¤F¬Ý¤£À´ªº¤§¥~
§Ú³o­Ó·s¤â´N¬O¤£·|¼g§r
³Â·Ð¦U¦ì¤F

Sub ¼g¤J¸ê®Æ()
If Range("A:A").End(xlDown).Row = 65536 Then
End If
Dim a(10)
a(0) = Range("B5")
a(1) = Range("D5")
a(2) = Range("J5")
a(3) = Range("C6")
a(4) = Range("B9")
a(5) = Range("D9")
a(6) = Range("E9")
a(7) = Range("F9")
a(8) = Range("G9")
a(9) = Range("H9")
a(10) = Range("I9")


Sheets("®w¦s°O¿ý").Select

End Sub

GBKEE

¦^´_ 33# guaga
½ÐªþÀɬݬÝ

guaga

¦^´_ 32# GBKEE
¦]¬°A:A  ¦³±a¤½¦¡ ©Ò¥H¤£¯à¥Î³o­Ó¤èªk§â¤½¦¡²M±¼
©Ò¥H·|¶Ã¶]ªº­ì¦] ¬O¦]¬°A:A ¦³±a¤½¦¡ªºÃö«Y¶Ü

GBKEE

¦^´_ 31# guaga
¥i¯à¬OSheets("­qÁÊ°O¿ý")AÄ椤ªºÀx¦s®æ¦³§¹¥þ¬OªÅ¥Õ¦r¤¸ªº¦r¦ê(¤£·|Åã¥Ü,©Ò¥H¬Ý¤£¨£)
°õ¦æ¤@¦¸³oµ{§Ç,¥i®ø°£ªÅ¥Õªº¦r¤¸

LTrim¡BRTrim »P Trim ¨ç¼Æ
¶Ç¦^¤@­Ó¨S¦³«eÀYªÅ¥Õ (LTrim)¡B«á­±ªÅ¥Õ (RTrim) ©Î«e«á§¡µLªÅ¥ÕªºVariant (String)¡A¨ä¤¤©Ò§t¬°µ¹©wªº¦r¦ê

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim E As Range
   
4.     With Sheets("­qÁÊ°O¿ý")
   
5.         For Each E In .Range("A:A").SpecialCells(xlCellTypeConstants, 3)
   
6.             E = Trim(E)
   
7.         Next
   
8.         MsgBox Range("A65536").End(xlUp).Address
   
9.    End With
   
10. End Sub

½Æ»s¥N½X

guaga

¦^´_ 30# GBKEE

·PÁª©¥D,§Ú§ï¦¨
Sheets("­qÁÊ°O¿ý").Cells([A65536].End(3).Row + 1, 2).Resize(1, UBound(arr) + 1) = arr
¤£¹L³o­Ó¦³®É­Ô³£·|¸õ¨ì A1048576ªºÀx¦s®æ¥h
¦³¤°»ò¿ìªk¸Ñ¨M?

GBKEE

¦^´_ 29# guaga
sheet ­qÁÊ°O¿ý,¨ºÃä¦h¥[¤@Äæ:  ³oarr¬O§A¨ÌSheets("­qÁÊ°O¿ý")Äæ¦ì¤º®e©Ò«ü©wªº¤¸¯À
§A¬O­n­×¥¿ ³oarr, °t¦X³o [¦h¥[¤@Äæ]
­ì¥»³oarr = Array(.[L4], .[N4], .[L7].Text, .Cells(i, "A"), .Cells(i, "C"), .Cells(i, "E"), .Cells(i, "H"), .Cells(i, "I"), "=RC[-1]*RC[-2]", .Cells(i, "J"), .Cells(i, "L"))
Sheets("­qÁÊ°O¿ý").Cells([A65536].End(3).Row + 1, 1).Resize(1, UBound(arr)+1) = arr

¥i¬OÁÙ¬O±qAÄæ¶}©l¶ñ? : Cells([A65536].End(3).Row + 1, 1) => Cells(¦C¼Æ, Äæ¼Æ):   ¦C¼Æ->¼Æ¦r ,Äæ¼Æ->¼Æ¦r,¦r¦ê-> 1="A"Äæ ,2="B"Äæ ,27="AA"Äæ

guaga

¦^´_ 28# GBKEE
§Ú¤F¸Ñ¤F  ¯uªº«D±`ÁÂÁª©¥D
¤£¹LÁÙ¦³­Ó°ÝÃD·Q½Ð±Ð¤@¤U  ¦pªG­n¦b  sheet ­qÁÊ°O¿ý  ¨ºÃä¦h¥[¤@Äæ
§ï¦¨
Sheets("­qÁÊ°O¿ý").Cells([B65536].End(3).Row + 1, 1).Resize(1, UBound(arr)) = arr

¥i¬OÁÙ¬O±qAÄæ¶}©l¶ñ?

GBKEE

¦^´_ 27# guaga
¨Ò AR(0 TO 10) ³o°}¦C¦@¦³11­Ó¤¸¯À :  °}¦C¤U­­¯Á¤Þ­È=0  , °}¦C¤W­­¯Á¤Þ­È=10  
UBound(arr): UBound¶Ç¦^°}¦C¤W­­¯Á¤Þ­È

1. Option Explicit
   
2. Sub TEST1()
   
3.     Dim arr, i As Integer
   
4.     With Sheets("­qÁʳæ")
   
5.         For i = 10 To Application.CountA(.[A10:A16]) + 9
   
6.             arr = Array(.[L4], .[N4], .[L7].Text, .Cells(i, "A"), .Cells(i, "C"), .Cells(i, "E"), .Cells(i, "H"), .Cells(i, "I"), "=RC[-1]*RC[-2]", .Cells(i, "J"), .Cells(i, "L"))
   
7.             Sheets("­qÁÊ°O¿ý").Cells([A65536].End(3).Row + 1, 1).Resize(1, UBound(arr) + 1) = arr
   
8.         Next
   
9.     End With
   
10. End Sub

½Æ»s¥N½X

guaga

¦^´_ 26# GBKEE

MsgBox .[B15].End(xlUp).Row      ³o¬q ¥[¶i¥hªº¸Ü ¶]°_¨Ó¥u·|Åã¥Ü¤@¬q
«á¨Ó§Ú§ï¦¨

    Option Explicit
        Sub TEST1()
            Dim arr, i As Integer
            With Sheets("­qÁʳæ")
                For i = 10 To Application.CountA(.[A10:A16]) + 9
                    arr = Array(.[L4], .[N4], .[L7].Text, .Cells(i, "A"), .Cells(i, "C"), .Cells(i, "E"), .Cells(i, "H"), .Cells(i, "I"), .Cells(i, "J"), .Cells(i, "L"))
                        
                    Sheets("­qÁÊ°O¿ý").Cells([A65536].End(3).Row + 1, 1).Resize(1, UBound(arr)) = arr
                Next
            End With
        End Sub

¤£¹L³ÆµùÄæ¶]¤£¥X¨Ó­C  ¬O¤£¬O¤]­n¼Ð¬W text?

GBKEE

¦^´_ 25# guaga
½Ð¦b§AªþÀɤW¸Õ¸Õ¬Ý

1. Option Explicit
   
2.     Sub TEST1()
   
3.         Dim arr, i As Integer
   
4.         With Sheets("­qÁʳæ")
   
5.         MsgBox .[B15].End(xlUp).Row                             '¦]AÄæ,BÄæ ¬O¦X¨ÖªºÀx¦s®æ,BÄæ¬OªÅ¥Õªº
   
6.         MsgBox .[A17].End(xlUp).Row                             '¦p¹Ï¥Ü .[A10:A16] ³£¦³¸ê®Æ =>10
   
7.             For i = 10 To Application.CountA(.[A10:A16]) + 9    'CountA : ­pºâ[A10:A16]ªº¸ê®Æ¼Æ(¸ê®Æ¤@©w­n¬O³sÄòªº)
   
8.                 arr = Array(.[L4], .[N4], .[L7].Text, .Cells(i, "A"), .Cells(i, "C"), .Cells(i, "E"), .Cells(i, "H"), .Cells(i, "I"), .Cells(i, "J"), .Cells(i, "L"))
   
9.                     '.[L7]¸ê®Æ¤é´Á,¬O¼Æ¦r,.[L7].Text ¥i¶Ç¦^¤é´Áªº®æ¦¡
   
10.                 Sheets("­qÁÊ°O¿ý").Cells([A65536].End(3).Row + 1, 1).Resize(1, UBound(arr)) = arr
    
11.             Next
    
12.         End With
    
13.     End Sub

½Æ»s¥N½X

¤U¸ü (79.21 KB)

2013-11-26 12:30