AleemAM123
09-24-2011, 06:27 PM
I need some help with a graph. I am drawing a graph from the table on GCCTable. The graph uses column A and column H, the number of rows can change and be more/less. I want to draw a graph on this using vba, Is there a better way to do this other than the following:
Sub GrandCompCurve()
'
' GrandCompCurve Macro
'
'
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim StreamCount As Integer
'Sheets("GCCurve").Select
'ActiveWindow.SelectedSheets.Delete
'Sheets("GCCTable").Select
Range("A4").Select
'Worksheets("1cdu").Range("l25") = "=[" & FileName2 & "]Sheet1!R44C7"
'count the number of cells from A6 and below
Range(Selection, Selection.End(xlDown)).Select
'number of rows
StreamCount = Selection.Count
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("GCCTable").Range("A4:H14"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=GCCTable!R4C8:R" & StreamCount + 3 & "C8"
ActiveChart.SeriesCollection(1).Values = "=GCCTable!R4C1" & ":R" & StreamCount + 3 & "C1"
ActiveChart.SeriesCollection(1).Name = "=""GCC"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GCCurve"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Grand Composite Curve"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Energy"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Shifted Temperature"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationManual
End With
End Sub
I would like to automate the graphs on sheet CCC as well. The number of rows can vary here as well.
Any help would be great, thanks.:friends:
Sub GrandCompCurve()
'
' GrandCompCurve Macro
'
'
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim StreamCount As Integer
'Sheets("GCCurve").Select
'ActiveWindow.SelectedSheets.Delete
'Sheets("GCCTable").Select
Range("A4").Select
'Worksheets("1cdu").Range("l25") = "=[" & FileName2 & "]Sheet1!R44C7"
'count the number of cells from A6 and below
Range(Selection, Selection.End(xlDown)).Select
'number of rows
StreamCount = Selection.Count
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("GCCTable").Range("A4:H14"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=GCCTable!R4C8:R" & StreamCount + 3 & "C8"
ActiveChart.SeriesCollection(1).Values = "=GCCTable!R4C1" & ":R" & StreamCount + 3 & "C1"
ActiveChart.SeriesCollection(1).Name = "=""GCC"""
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="GCCurve"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Grand Composite Curve"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Energy"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Shifted Temperature"
End With
With ActiveChart.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With
With ActiveChart.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With
ActiveChart.HasLegend = False
ActiveChart.Axes(xlCategory).Select
With Selection.Border
.Weight = xlHairline
.LineStyle = xlAutomatic
End With
With Selection
.MajorTickMark = xlOutside
.MinorTickMark = xlNone
.TickLabelPosition = xlNextToAxis
End With
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.Weight = xlThin
.LineStyle = xlAutomatic
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
With Selection.Border
.ColorIndex = 57
.Weight = xlMedium
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlAutomatic
.MarkerForegroundColorIndex = xlAutomatic
.MarkerStyle = xlAutomatic
.Smooth = False
.MarkerSize = 7
.Shadow = False
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationManual
End With
End Sub
I would like to automate the graphs on sheet CCC as well. The number of rows can vary here as well.
Any help would be great, thanks.:friends: