- 帖子
- 27
- 主題
- 11
- 精華
- 0
- 積分
- 38
- 點名
- 0
- 作業系統
- window
- 軟體版本
- window7
- 閱讀權限
- 10
- 性別
- 男
- 註冊時間
- 2019-12-12
- 最後登錄
- 2021-4-21
|
如何動辨別有多少sheet畫幾次圖
各位大大好
因工作需求需要建立一個自動繪圖的VBA程式
已在此站上尋獲可自動輸入資料的方式(感謝大大)
但問題是每次輸日的資料多少不一定(ps:一筆資料,一個sheet;有時3個sheets 有時5個sheet,最多不超過10組)
每個sheet要畫10張圖
我已錄製繪圖的巨集,但要只會重複固定的sheet(ex: sheet1......plot; sheet2......plot, sheet3...plot''''''停止)
但會因為每次資料sheet多少不一定;而無法寫死plot在一定的sheets數量(有時3sheets, 有時4sheets, 有時6sheets, 最多10sheets)
請問該如何解阿
以下附上程式
希望格位大大幫忙
5.畫圖.....................................................主要是這一塊(都劃在一張sheet上),想自動辦別有多少sheet而畫幾次
Sub Plot()
'''''''''''''''''''''''''''''''''''''''''Chart(1) Ave. G.R. / HSP ADD'''''''''''''''''''''''''
Sheets("工作表1 (2)").Activate
Range("AA20").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""GSP"""
ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$AB$3:$AB$17"
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""HGSP"""
ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$AC$3:$AC$17"
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""LGSP"""
ActiveChart.SeriesCollection(3).XValues = "='工作表1 (2)'!$AA$3:$AA$17"
ActiveChart.SeriesCollection(3).Values = "='工作表1 (2)'!$AD$3:$AD$17"
ActiveChart.SeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
End With
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""Ave.G.R."""
ActiveChart.SeriesCollection(4).XValues = "='工作表1 (2)'!$A$2:$A$20000"
ActiveChart.SeriesCollection(4).Values = "='工作表1 (2)'!$D$2:$D$20000"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(5).Name = "=""HSP"""
ActiveChart.SeriesCollection(5).XValues = "='工作表1 (2)'!$A$2:$A$20000"
ActiveChart.SeriesCollection(5).Values = "='工作表1 (2)'!$O$2:$O$20000"
ActiveChart.SeriesCollection(5).AxisGroup = 2
ActiveChart.ApplyLayout (4)
Dim xRg As Range
Dim xChart As ChartObject
Set xRg = Range("AA20:AG40")
Set xChart = ActiveSheet.ChartObjects(1)
With xChart
.Top = xRg(1).Top
.Left = xRg(1).Left
.Width = xRg.Width
.Height = xRg.Height
End With
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = "Ave.G.R. vs HSP"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "G.R.(mm/hr)"
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "Length(mm)"
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = 0
ActiveChart.Axes(xlCategory).MaximumScale = 1100
ActiveChart.Axes(xlCategory).MajorUnit = 100
ActiveChart.Axes(xlCategory).MinorUnit = 50
ActiveChart.Axes(xlCategory).CrossesAt = 0
ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
Sheets("工作表1").Activate
'''''''''''''''''''''''''''''''''''''''''Chart(2) HSP'''''''''''''''''''''''''
Sheets("工作表1 (2)").Activate
Range("AI20").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Delta_HSP"""
ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$AK$3:$AK$17"
ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$AM$3:$AM$17"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Delta_HSP(SOP)"""
ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$AK$3:$AK$17"
ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$AN$3:$AN$17"
ActiveChart.ApplyLayout (4)
Dim yRg As Range
Dim yChart As ChartObject
Set yRg = Range("AI20:AN40")
Set yChart = ActiveSheet.ChartObjects(2)
With yChart
.Top = yRg(1).Top
.Left = yRg(1).Left
.Width = yRg.Width
.Height = yRg.Height
End With
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = "Delat_HSP"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Delta_HSP(sp)"
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "Length(mm)"
ActiveSheet.ChartObjects(2).Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = 0
ActiveChart.Axes(xlCategory).MaximumScale = 1100
ActiveChart.Axes(xlCategory).MajorUnit = 100
ActiveChart.Axes(xlCategory).MinorUnit = 50
ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
'''''''''''''''''''''''''''''''''''''''''''''''''''Chart(3)Pressure torr ADD'''''''''''''''''''''''''
Sheets("工作表1 (2)").Activate
Range("AP20").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""F/T Press."""
ActiveChart.SeriesCollection(1).XValues = "='工作表1 (2)'!$A$2:$A$20000"
ActiveChart.SeriesCollection(1).Values = "='工作表1 (2)'!$I$2:$I$20000"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Press. SP"""
ActiveChart.SeriesCollection(2).XValues = "='工作表1 (2)'!$A$2:$A$20000"
ActiveChart.SeriesCollection(2).Values = "='工作表1 (2)'!$P$2:$P$20000"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""Press._SOP"""
ActiveChart.SeriesCollection(3).XValues = "='工作表1 (2)'!$AP$3:$AP$17"
ActiveChart.SeriesCollection(3).Values = "='工作表1 (2)'!$AT$3:$AT$17"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = "=""Throttle Pos.(%)"""
ActiveChart.SeriesCollection(4).XValues = "='工作表1 (2)'!$A$2:$A$20000"
ActiveChart.SeriesCollection(4).Values = "='工作表1 (2)'!$T$2:$T$20000"
ActiveChart.SeriesCollection(4).AxisGroup = 2
ActiveChart.ApplyLayout (4)
Dim zRg As Range
Dim zChart As ChartObject
Set zRg = Range("AP20:AU40")
Set zChart = ActiveSheet.ChartObjects(3)
With zChart
.Top = zRg(1).Top
.Left = zRg(1).Left
.Width = zRg.Width
.Height = zRg.Height
End With
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = "Press. vs Throttle Position Variation"
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Press.(torr)"
ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
Selection.Caption = "Length(mm)"
ActiveSheet.ChartObjects(3).Activate
ActiveChart.Axes(xlCategory).Select
ActiveChart.Axes(xlCategory).MinimumScale = 0
ActiveChart.Axes(xlCategory).MaximumScale = 1100
ActiveChart.Axes(xlCategory).MajorUnit = 100
ActiveChart.Axes(xlCategory).MinorUnit = 50
ActiveChart.SetElement (msoElementPrimaryValueGridLinesMajor)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesMajor)
End Sub |
|
發表於 2020-1-10 15:04
| 
