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

[µo°Ý] ½Ð°Ý¸Ó¦p¦ó¥ÎVBA¦Û°Ê¤Æ¥XªÑ²¼K½u¹Ï©O¡H

[µo°Ý] ½Ð°Ý¸Ó¦p¦ó¥ÎVBA¦Û°Ê¤Æ¥XªÑ²¼K½u¹Ï©O¡H

¥»©«³Ì«á¥Ñ j1221 ©ó 2011-1-16 23:50 ½s¿è

¦U¦ì«e½ú¦n¡A¤p§Ì¤S¨Ó¥oÂZ¤F¡A³o¦¸§Úªº°ÝÃD¦p¤U¡G

§Ú§Q¥ÎVBA¥ý§ì¥X§Ú­nªº¸ê®Æ«á¡AµM«á¦A±N©Ò§ì¥X¨Óªº¸ê®Æ¤Æ¦¨K½u¹Ï

¦bµe¹Ïªº³¡¥÷¡A¦]¬°¤p§Ì¤£ª¾¹D¸Ó«ç»ò¼g¡A©Ò¥H§Ú¬O§Q¥Î¿ý»s¥¨¶°ªº¥\¯à¡A§â¥¨¶°»s§@¥X¨Ó¡C

¦ý»s§@¦n¤§«á¡A·í§Ú·Q­n­«·s°õ¦æ¤@¦¸¥¨¶°ªº®É­Ô¡A¸õ¥X¹ï¸Üµøµ¡¡uChartType ¤èªk(Chartª«¥ó)¥¢±Ñ¡v

¥i§_½Ð°Ý¬O¬Æ»ò°ÝÃD¡A¸Ó¦p¦ó¸Ñ¨M©O¡H

·Ð½Ð°Ñ¾\ªþ¥ó(Module 2)

test1.rar (170.94 KB)
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

Hello,

¦pªG¬O¦b¬¡­¶Ã¯¸Ìªº¸ê®Æ, «Øijª½±µ¨Ï¥Î¹Ïªíªº¤è¦¡Ã¸»s´N¥i¥H¤F.
³o¼Ë¤l¤ñ¸û²³æ§Ö³t.

TOP

¦^´_ 1# j1221
¥ý§ïÅܸê®Æ¼Æ¦C¦A³]©w¹ÏªíÃþ«¬
  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. ' Shih-Hao ¦b 2011/1/16 ¿ý»sªº¥¨¶°
  5. '

  6. '
  7.     Charts.Add
  8.     'ActiveChart.ChartType = xlStockOHLC  '¨ú®ø³o¦æ

  9.     ActiveChart.SetSourceData Source:=Sheets("76C").Range("A1:N22"), PlotBy:= _
  10.         xlColumns
  11.     ActiveChart.SeriesCollection(5).Delete
  12.     ActiveChart.SeriesCollection(5).Delete
  13.     ActiveChart.SeriesCollection(5).Delete
  14.     ActiveChart.SeriesCollection(5).Delete
  15.     ActiveChart.SeriesCollection(5).Delete
  16.     ActiveChart.SeriesCollection(1).XValues = "='76C'!R2C1:R22C1"
  17.     ActiveChart.SeriesCollection(2).XValues = "='76C'!R2C1:R22C1"
  18.     ActiveChart.SeriesCollection(3).XValues = "='76C'!R2C1:R22C1"
  19.     ActiveChart.SeriesCollection(4).XValues = "='76C'!R2C1:R22C1"
  20.     ActiveChart.Location Where:=xlLocationAsObject, Name:="76C"
  21.     With ActiveChart
  22.         .HasTitle = False
  23.         .Axes(xlCategory, xlPrimary).HasTitle = False
  24.         .Axes(xlValue, xlPrimary).HasTitle = False
  25.         .ChartType = xlStockOHLC   '³o¸Ì¤~§ïÅÜÃþ«¬
  26.     End With
  27.     ActiveChart.HasLegend = False
  28.     Windows("test1.xls").SmallScroll Down:=7
  29.     ActiveSheet.Shapes(1).IncrementLeft -168.75
  30.     ActiveSheet.Shapes(1).IncrementTop 269.25
  31.     Windows("test1.xls").SmallScroll Down:=6
  32.     ActiveSheet.Shapes(1).ScaleWidth 1.53, msoFalse, msoScaleFromTopLeft
  33.     ActiveSheet.Shapes(1).ScaleHeight 1.36, msoFalse, msoScaleFromTopLeft
  34.     ActiveChart.PlotArea.Select
  35.     With Selection.Border
  36.         .ColorIndex = 16
  37.         .Weight = xlThin
  38.         .LineStyle = xlContinuous
  39.     End With
  40.     With Selection.Interior
  41.         .ColorIndex = 2
  42.         .PatternColorIndex = 1
  43.         .Pattern = xlSolid
  44.     End With
  45.     With Selection.Border
  46.         .ColorIndex = 16
  47.         .Weight = xlThin
  48.         .LineStyle = xlContinuous
  49.     End With
  50.     With Selection.Interior
  51.         .ColorIndex = 40
  52.         .PatternColorIndex = 1
  53.         .Pattern = xlSolid
  54.     End With
  55.     ActiveChart.Axes(xlCategory).Select
  56.     Selection.TickLabels.NumberFormatLocal = "yyyy-mm-dd"

  57. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

·P¿EHsiehªO¥D¡A¨ü±Ð¤F....

¦ý¥i§_½Ð°Ý±z¡A¬°¦ó­n¡u¥ý§ïÅܸê®Æ¼Æ¦C¦A³]©w¹ÏªíÃþ«¬¡v¤~¹ï©O¡H

¦A¦¸·PÁÂ
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

¦^´_ 4# j1221


    k½u¹Ï¸ê®Æ¥²»Ý¨Ì§Ç±Æ¦C
§A­Yª½±µ¥Î©Ò¦³Äæ¦ìª½±µ»s¹Ï·|¥H¹w³]ªºª½½Õ¹Ï§e²{
­Yª½±µÂà´«Ãþ«¬¡AEXCEL¤]·|¥X²{ĵ§iµøµ¡¡A­n¨DÅܧó¸ê®Æ½d³ò
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 5# Hsieh


Hsieh¤j·P®¦¡A¤S¾Ç¨ì¤@¼ËªF¦è¡C¥i§_¦A½Ð±Ð±z¦p¦ó«ü©w¤u§@ªíªº°ÝÃD¡C§Ú¥ý©w¸q¤F¨â­ÓÅܼÆc ©M op¡Ac ¬°¼Æ¦r¡Aop¬°¤å¦r
¡CµM«á§Ú§Q¥Î ThisWorkbook.ActiveSheet.Name = c & op ©R¦W¥Ø«eªº¤u§@ªí¡C¦ýª¾«á§Ú·Q­n¦A«ü©w³o­Ó¤u§@ªí®É¸Ó¦p¦ó«ü©w¡H

§Ú¬O¥Î Sheets("c & op").Cells(i, 1)....
   
·Ð½Ð±z°Ñ¦Òªþ¥ó¡A¦bModule 1ªº¦C32¡A·PÁ¡C:D

test1.rar (170.74 KB)
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

¦^´_ 6# j1221


    Sheets(c & op).Cells(i, 1).PasteSpecial
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 7# Hsieh

Hsieh¤j©êºp¡A½Ð°Ý¦bø»s¹Ïªí¸Ì­±ªº³¡¥÷¸Ó«ç»ò§ï¡H¤ñ¦p»¡³o­Ó¡H

ActiveChart.SeriesCollection(1).XValues = "='c & op'!R2C1:R22C1"
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

¦^´_ 8# j1221


    ActiveChart.SeriesCollection(1).XValues = "='" & c & op & "'!R2C1:R22C1"
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


Hsieh¤j¡A©êºp¤S¦³°ÝÃD¤F.....³o¦¸°ÝÃD¥X¦b§Ú¶]§¹d=1¡A¶i¤Jd=2®É¡C¦b

    With Sheets("data")
                Cells(1, 1).EntireRow.Select
                Selection.Copy
                Sheets.Add After:=Sheets("data")
                ActiveSheet.Cells(1, 1).PasteSpecial
                ThisWorkbook.ActiveSheet.Name = c & op
   End With

¥X²{¡uClass RangeªºSelect ¤èªk¥¢±Ñ¡vªº°T®§¡A¬°¤°»ò²Ä¤@°é¨S¦³°ÝÃD¡A²Ä¤G°éªº®É­Ô´N·|¥X¨Ó¿ï¾Ü¿ù»~¡H¡H·PÁ±z
§Ú·R³Â»¶®a±Ú°Q½×°Ï¡I¡I

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD