Coding for Charting Graphs

[ryanwch]

Hello. I am trying to created stacked graphs using Excel. This is the script I have recorded for a specific chart I did.

Sub Graph()
'
' Graph Macro
'


'
Range("AO30:AQ43").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnStacked
ActiveChart.SetSourceData Source:=Range("Summary!$AO$30:$AQ$43")
ActiveSheet.Shapes("Chart 7").IncrementLeft 126.1764566929
ActiveSheet.Shapes("Chart 7").IncrementTop 127.0588188976
ActiveSheet.Shapes("Chart 7").ScaleWidth 1.1617646544, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 7").ScaleWidth 1.0168776268, msoFalse, _
msoScaleFromTopLeft
ActiveChart.ApplyLayout (3)
ActiveChart.SeriesCollection(1).XValues = "=Summary!$AN$31:$AN$43"
ActiveSheet.Shapes("Chart 7").IncrementLeft -42.352992126
ActiveSheet.Shapes("Chart 7").IncrementTop -1.7646456693
ActiveSheet.Shapes("Chart 7").ScaleWidth 1.0975106619, msoFalse, _
msoScaleFromTopLeft
ActiveChart.ChartTitle.Select
Selection.Caption = "ULR - "
ActiveChart.ChartTitle.Text = "ULR - Ultimate Basis "
Selection.Format.TextFrame2.TextRange.Characters.Text = "ULR - Ultimate Basis "
With Selection.Format.TextFrame2.TextRange.Characters(1, 21).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 21).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 7").ScaleHeight 1.0780231117, msoFalse, _
msoScaleFromBottomRight
ActiveSheet.Shapes("Chart 7").ScaleHeight 1.0341038396, msoFalse, _
msoScaleFromBottomRight
End Sub

I am trying to change the range to that it can select the number of columns of rows from the active cell. I would like to select 14 rows and 4 columns, including the active cell. Any help is appreciated.

 

[skywriter]

I'm just guessing since I don't know where in your code you are speaking of, and what you are trying to accomplish.

ActiveCell.Resize(14,4).Select

 

[ryanwch]

Yes, I would like to select the range of those cells but I have problem when it comes to this.


ActiveChart.SetSourceData Source:=Range("Summary!$AO$30:$AQ$43").

How do I modify the syntax so that it can read my new source?

 

[skywriter]

Do you want it to be based on an active cell, meaning a cell you select or based on something else, which I don't know what that would be.
If active cell then try this and see if it works.

Source:=Selection.Resize(14,4)

or

Dim rngSource As Range
Set rngSource = Range(ActiveSheet.Name & "!" & Selection.Resize(14, 4).Address)
Source:= rngSource