2012年9月20日星期四

EXCEL VBA 去除"$"的方法 replace

Sub 快速走勢圖()


Dim Sht As Worksheet

Set Sht = Worksheets(1)

Set Rng1 = Sht.UsedRange

Set Rng2 = Rng1.SpecialCells(xlCellTypeLastCell)

x = Rng2.Address



Set Rng = Selection

mystart = Rng.Address

mystart = Replace(mystart, "$", "", 1) ' 去除此之外符號成為相對公式

Range("P2").Select

Range("$P$2").SparklineGroups.Add Type:=xlSparkLine, SourceData:=mystart

走勢圖參數

ActiveCell.FormulaR1C1 = "q"

Range("P2").Select

Selection.AutoFill Destination:=Range("P2:P109")

Range("P2:P109").Select

Selection.ClearContents

Range("P2").Select





Set Rng1 = Nothing

Set Rng2 = Nothing

Set Sht = Nothing





End Sub

Public Sub 走勢圖參數()

Selection.SparklineGroups.Item(1).SeriesColor.ThemeColor = 5

Selection.SparklineGroups.Item(1).SeriesColor.TintAndShade = -0.499984740745262

Selection.SparklineGroups.Item(1).Points.Negative.Color.ThemeColor = 6

Selection.SparklineGroups.Item(1).Points.Negative.Color.TintAndShade = 0

Selection.SparklineGroups.Item(1).Points.Markers.Color.ThemeColor = 5

Selection.SparklineGroups.Item(1).Points.Markers.Color.TintAndShade = - _

0.499984740745262

Selection.SparklineGroups.Item(1).Points.Highpoint.Color.ThemeColor = 5

Selection.SparklineGroups.Item(1).Points.Highpoint.Color.TintAndShade = 0

Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.ThemeColor = 5

Selection.SparklineGroups.Item(1).Points.Lowpoint.Color.TintAndShade = 0

Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.ThemeColor = 5

Selection.SparklineGroups.Item(1).Points.Firstpoint.Color.TintAndShade = _

0.399975585192419

Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.ThemeColor = 5

Selection.SparklineGroups.Item(1).Points.Lastpoint.Color.TintAndShade = _

0.399975585192419

End Sub

 
妹咕數位學園歡迎網友們來信指教 妹咕信箱