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

[µo°Ý] °ÊºA­×¥¿¶×¤J¹Ïªíªº³Ì«á¸ê®Æ¦C¤§¦C¼Æ©Ò©µ¦ùªº°ÝÃD

¦^´_ 19# GBKEE
³Ìªñ¹ê»Ú¤@¤f®ð¨C¦¸ Run ¤F¤»­Ó¹Ïªí¡A¥B¬O½u¤WÀH®É­×¥¿¶×¤JÁ`µ§¼Æ (¦p¡G "²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows )¡A
µ²ªGµo²{¦b¨C¦¸¦Û°Ê§ó·s«á¡A³£ Focus ¦b³Ì«á¤@­Ó¹Ïªí¤W¡C¦pªG¦¹®É°Ê¤FÁä½L´N¥Xª¬ªp¤F¡F
Ä´¦p¡G ±z¥i¯à¦¹®É¦b Excel ¤W­×¥¿¬Y¨Ç¸ê®Æ¡A¦p¡G¨Ï¥Î DEL Áäµ¥¡A«K±N¸Ó³Ì«á Focus ªº¹Ïªíµ¹§R°£±¼¤F¡A
³o¤]¬O§Ú­è­è¨S¯d·N¦Óµo¥Íªº±¡ªp¡C µ²ªG¥u³Ñ¤U¤F¤­­Ó¹Ïªí¡A¸g¥J²ÓÆ[¹î¡Aµoı¥u­n VBA ¦Û°Ê°õ¦æ  
.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows) «á¡A´«¦¨²Ä¤­­Ó¹Ïªí (³Ì«á¤@­Ó¹Ïªí) ³Q
Focus ¤F¡C
½Ð°Ý§Ú­n¦p¦óÁ׶}¦¹§xÂZªº°ÝÃD¡C °²³] µe§¹¹Ïªí«á¡A¤U­Ó«ü¥O¡A¦p¡G Cells(1,1).Select ·íµM«K±N²¾Âà Focus ¨ì A1 Äæ¦ì¤W¤F¡A
¦ý¬O¦pªG±z¦b .SetSourceData °õ¦æ«e¡A¥¿¦b¾\Äý¨ä¥¦ªº¤u§@ªí³æ¡A«h±µ¤U¨Óªºµe­±«K·|³Q¤Á¨ì A1 Äæ¦ì¤W¤F¡C
³o¼Ë³B²z°Z¤£·P¨ì«D±`©_©Ç¡H ¦]¬°¾\Äý­¶­±¬ðµM³Q±j¨îÂಾ¤F¡I
½Ð°Ý¦³§_§PÂ_­ì¥»¾\Äý­¶­±¡B©ÎªÌ¬O­ì¥»´å¼Ð¦b­þ¸Ì¡Aø§¹¹Ï«á¦Û°Ê¤Á¦^¨ì­ì­¶­±¡H ©ÎªÌ¬OÁÙ¦³§ó´Îªº Idea¡H
ÁÂÁ±z¤F¡I

TOP

¦^´_ 21# c_c_lai
¤W¶ÇÀɮ׬ݬÝ

TOP

¦^´_ 22# GBKEE

¸Ó±¡ªp»Ý¦b½L¤¤¹B§@®É¤~Æ[¹î±o¨ì¡A¦]¬Q¤é½L¤¤¤£¤p¤ß§R±¼³Ì«áªº¨º±i¹Ïªí¡A©È¤£«ÈÆ[¡A©Ò¥H¤µ¤Ñ§Ú¦AÆ[¹î¬Ý¬Ý¡A
¦pªG¤´µM¤@¼Ë§Ú¦A¦V±z¸[§i¡AÁÂÁ±z¡I

TOP

¦^´_ 22# GBKEE
Æ[¹îµ²ªG¦p¡G
1).   §Ú¬G·N¥ý±N´å¼Ð Focus ¨ì AZ2 Äæ¦ì¤W¡A°µ¬°¤è«K¿ëÃѤ§¥Î¡C


2).  ±µ¤U¤£¥h¸IIJ¥ô¦ó©N©N¡AÆ[¹î»ô¸ê®Æ°ÊºA³B­ù¡A¥u­n¥¦¤@¦^¼g getEndRows ¡A
      °Ê§@µ²§ô«á«K·|¦Û°Ê  Focus ¨ì "¥D¤O¡B´²¤á¡B»P¦¨¥æ»ù¡B¶q" ªº¹Ïªí¤W¡C


½Ð±ÐÀ³¦p¦ó¥hÁקK¦¹ª¬ªp¡A¦Ó¥B¤]¤£·|¼vÅT¨ì­ì¥»¬d¬Ýªº­¶­±¡C
ÁÂÁ±z¡I
  1. Sub Test()
  2.     .
  3.     .
  4.     .
  5.     Call getEndRows("²Î­p¹Ïªí")
  6.     Call getEndRows("Omega")
  7.     .
  8. End Sub

  9. Sub getEndRows(sDraw As String)  
  10.     Dim oShape As Shape
  11.     Dim numChart As Integer
  12.     Dim totalRows As Single
  13.    
  14.     numChart = 0
  15.    
  16.     totalRows = Sheets("²Î­p¹Ïªí").Range("B" & Rows.Count).End(xlUp).Row     ' ¶Ç¦^ B Äæ©Ò¨Ï¥ÎÀx¦s®æ¤§³Ì«á¤@®æ¤§¦C¸¹
  17.     Sheets(sDraw).Select
  18.             
  19.     For Each oShape In ActiveSheet.Shapes
  20.         If oShape.Type = 3 Then
  21.             numChart = numChart + 1
  22.             
  23.             ActiveSheet.ChartObjects(oShape.Name).Activate   
  24.                
  25.             Select Case numChart
  26.                 Case 1
  27.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AA$1:²Î­p¹Ïªí!$AA$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¥D¤O¬É¤J
  28.                 Case 2
  29.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AB$1:²Î­p¹Ïªí!$AB$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¤O®t
  30.                 Case 3
  31.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AC$1:²Î­p¹Ïªí!$AC$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ®ø¤Æ¤O
  32.                 Case 4
  33.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AD$1:²Î­p¹Ïªí!$AD$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X §¡®t(¤j¤á)
  34.                 Case 5
  35.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$F$1:²Î­p¹Ïªí!$F$" & totalRows & ", ²Î­p¹Ïªí!$I$1:²Î­p¹Ïªí!$J$" & totalRows & ", ²Î­p¹Ïªí!$V$1:²Î­p¹Ïªí!$V$" & totalRows)  ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¦¨¥æ»ù¡B¥D¤O¬É¤J¡B´²¤á¤è¦V¡B¥H¤Î¦¨¥æ¶q¡C
  36.                 Case Else
  37.                     ActiveChart.SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$F$1:²Î­p¹Ïªí!$F$" & totalRows & ", ²Î­p¹Ïªí!$V$1:²Î­p¹Ïªí!$V$" & totalRows)   ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¦¨¥æ»ù
  38.             End Select
  39.         End If
  40.         
  41.         If (sDraw = "Omega" And numChart = 5) Then Exit For
  42.     Next
  43. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-19 12:31 ½s¿è

¦^´_ 24# c_c_lai
°ÝÃD¦b
ActiveSheet.ChartObjects(oShape.Name).Activate
  1. Sub getEndRows(sDraw As String)
  2.     Dim oShape As Shape
  3.     Dim numChart As Integer
  4.     Dim totalRows As Single
  5.     numChart = 0
  6.     totalRows = Sheets("²Î­p¹Ïªí").Range("B" & Rows.Count).End(xlUp).Row     ' ¶Ç¦^ B Äæ©Ò¨Ï¥ÎÀx¦s®æ¤§³Ì«á¤@®æ¤§¦C¸¹
  7.     Sheets(sDraw).Select
  8.     For Each oShape In ActiveSheet.Shapes
  9.         If oShape.Type = 3 Then
  10.             numChart = numChart + 1
  11.             With ActiveSheet.ChartObjects(oShape.Name).Chart
  12.                 Select Case numChart
  13.                     Case 1
  14.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AA$1:²Î­p¹Ïªí!$AA$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¥D¤O¬É¤J
  15.                     Case 2
  16.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AB$1:²Î­p¹Ïªí!$AB$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¤O®t
  17.                     Case 3
  18.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AC$1:²Î­p¹Ïªí!$AC$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ®ø¤Æ¤O
  19.                     Case 4
  20.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$AD$1:²Î­p¹Ïªí!$AD$" & totalRows) ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X §¡®t(¤j¤á)
  21.                     Case 5
  22.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$F$1:²Î­p¹Ïªí!$F$" & totalRows & ", ²Î­p¹Ïªí!$I$1:²Î­p¹Ïªí!$J$" & totalRows & ", ²Î­p¹Ïªí!$V$1:²Î­p¹Ïªí!$V$" & totalRows)  ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¦¨¥æ»ù¡B¥D¤O¬É¤J¡B´²¤á¤è¦V¡B¥H¤Î¦¨¥æ¶q¡C
  23.                     Case Else
  24.                         .SetSourceData Source:=Range("²Î­p¹Ïªí!$B$1:²Î­p¹Ïªí!$B$" & totalRows & ", ²Î­p¹Ïªí!$F$1:²Î­p¹Ïªí!$F$" & totalRows & ", ²Î­p¹Ïªí!$V$1:²Î­p¹Ïªí!$V$" & totalRows)   ' ¹Ï¥Ü·|¤À§OÅã¥Ü¥X ¦¨¥æ»ù
  25.                 End Select
  26.             End With
  27.         End If
  28.         If (sDraw = "Omega" And numChart = 5) Then Exit For
  29.     Next
  30. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-4-19 12:18 ½s¿è

¦^´_ 25# GBKEE
¦pªG§ï¦¨ ActiveSheet.ChartObjects(oShape.Name).Select ¥i¦æ§_¡H

¸g¹ê»Ú´ú¸Õ¡A .Activate = .Select µ²ªG¤@¼Ë¡A
À³¦p¦ó­×¥¿©O¡H ¦pªG±N¥¦ Mask ' ¹Ï§Î«K¨«¼Ë¤F¡I

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-19 12:31 ½s¿è

¦^´_ 26# c_c_lai
25# ªº  µ{¦¡½X §A¦³®M¥Î¸Õ¬Ý¬Ý¶Ü?
¸g¹ê»Ú´ú¸Õ¡A .Activate = .Select µ²ªG¤@¼Ë¡A???
With ActiveSheet.ChartObjects(oShape.Name).Chart    ¨Ã¨S¦³­n  Activate OR .Select

TOP

¦^´_ 27# GBKEE
¤Ó´Î¤F¡I°ÝÃD²×©ó¸Ñ¨M¤F¡C
½Ð±Ð±z¡G
¨Ï¥Î             With ActiveSheet.ChartObjects(oShape.Name).Chart    »P
¨Ï¥Î            ActiveSheet.ChartObjects(oShape.Name).Activate        
¦b¹ê°ÈÀ³¥Î­±¤W¡A¨ä·N¸q¤W¨ì©³¦³¬Æ»ò®t§O©O¡H With ActiveSheet.ChartObjects(oShape.Name).Chart  
·|¦Û¦æ¦æ¨Ï Select ¶Ü¡H

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-4-19 14:29 ½s¿è

¦^´_ 28# c_c_lai
ActiveSheet.ChartObjects(oShape.Name).Activate   
³o»yªkªº·N«ä: ±N¥Ø«eªº¹ÏªíÅܦ¨§@¥Î¤¤ªº¹Ïªí ,¿Ã¹õµe­±·|Âಾ¨ì¦¹¹Ïªí
With ActiveSheet.ChartObjects(oShape.Name).Chart     
³o»yªkªº·N«ä  ¦b¤@­Ó³æ¤@ª«¥ó©Î¤@­Ó¨Ï¥ÎªÌ¦Û­q«¬ºA¤W°õ¦æ¤@¨t¦Cªº³¯­z¦¡
¿Ã¹õµe­±¤£·|Âಾ¨ì¦¹¹Ïªí

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2012-4-19 16:18 ½s¿è

¦^´_ 29# GBKEE
¦A½Ð±Ð¤@¤U¡G
§Ú°õ¦æø¹Ïªºµ{§Ç¬O¡G
1)  ¸ü¤J¸ê®Æ¥þ³¡©ñ¸m¦b "²Î­p¹Ïªí"¤u§@ªí³æ¡A«ö¶s¿ï¶µ¥þ³¡¦w´¡¦b"Omega"¤u§@ªí³æ¤W¡C
2) ¦pªG¿ï«ö "¥þ³¡­«Ã¸"¡A ¥¦·|¥ýø»s "²Î­p¹Ïªí"¤u§@ªí³æ¤Wªº¹Ïªí¡A±µ¤U¨Ó¦A¥hø»s"Omega"¤u§@ªí³æ¤Wªº¹Ïªí
    (¨â­Ó¤u§@ªí³æªí³æ¤W°õ¦æªºÃ¸¹Ï¼Ò²Õ¬O¤@¼Ëªº¡A¥u¬O¤À§O±N¨â­Ó¤u§@ªí³æ¤Wø»s¦P¼Ë¹Ïªí)
3) ¦b"²Î­p¹Ïªí"¤u§@ªí³æ¤W¦¨¥\¦aø»s¥X¤»­Ó¹Ïªí«á¡A±µµÛ¤S­n¦b"Omega"¤u§@ªí³æ¤W¹º¥X¦P¼Ë¤»­Ó¬Û¦P¹Ïªí®É¡A
     «o¥X²{¤@¿ù»~°T®§  -----  ¨t²Î¿ù»~ &H80040000 (-2147221504)¡C  µL®Äªº OLEVERB µ²ºc
     «ö§¹½T»{¶s«á¡A¤£¤©²z·|¦b°õ¦æ¤@¦¸¡A±µµÛ¤S¥X²{¤@¿ù»~µøµ¡  -----  400
4) Æ[¹îµ²ªG¬O¡G"²Î­p¹Ïªí"¤u§@ªí³æ¤W¦¨¥\¦aø»s¥X¤F¤»­Ó¥¿½T¹Ïªí¡A¦Ó¦b"Omega"¤u§@ªí³æ¤W¦³®É¥u²£¥Í¤F¤@±i¹Ïªí¡A
    ¦³®É«o³£²£¥ÍÁA¤»­Ó¹Ïªí¡A¦ý¨ä¼Æ¦C§¡«D«ü©wªº¼Æ¦C¡A¦Ó¬O"Omega"¤u§@ªí³æ¤W¥»¨­¤§¨ä¥L¥Î³~ªº¼Æ¾Ú¸ê®Æ (¶Ã§ìOmegaªº¨Ï¥Î¸ê®Æ)¡C
½Ð°Ý³o¬O¦óºØ±¡ªp¤~·|¦p¦¹¡H

5) ±N¨ä©Ò¦³¬ÛÃö¼Ò²Õ©ñ¤J¥t¤@ Excel ªí¤º°õ¦æ¡A³£¥¿±`¡C
     ¦ý¤£¦Pªº¬O¡G ³o­Óªí³æªº"Omega"¤u§@ªí³æ¤W¨ÃµL¦s©ñ¥ô¦ó¸ê®Æ¡A¶ÈÂ\©ñ«ö¶s¦Ó¤w¡C   (±N¥¦ªþ¤W°Ñ¦Ò¡A©Ò¦³¼Ò²Õ§¡¤@­P)
    (¬°¤F»P­ì©l¤@­P¡A"Omega" ªº¹Ïªí¦ì¸m¬O¦b AI:AF ¶¡)
¥D¤O¡B´²¤á¡B¦¨¥æ»ù¡B¶q.rar (198.08 KB)
(³Ì«á¤äªþ¥ó¤~¬O)

¥D¤O¡B´²¤á¡B¦¨¥æ»ù¡B¶q.rar (198.08 KB)

TOP

        ÀR«ä¦Û¦b : ¤£­n¤p¬Ý¦Û¤v¡A¦]¬°¤H¦³µL­­ªº¥i¯à¡C
ªð¦^¦Cªí ¤W¤@¥DÃD