series error

S

sunshine076

Registered User.
Local time
Today, 09:52
Joined
Apr 6, 2009
Messages
160
I have converted a macro that runs a chart with everything working properly. However, I have noticed that the range gives you a specific range and what I am looking is to be able to use the offset formula where if we lose or gain a row it will automatically update after the entry. How can I chage the coding to work and do what I want it to do?

Code: 
Sub CharE()
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("OVL").Range("N14:N87"), PlotBy:= _
        xlColumns
    ActiveChart.SeriesCollection(1).XValues = "=OVL!R14C2:R87C2"
    ActiveChart.SeriesCollection(1).Name = "=""Char E"""
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Char E"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Char E Run Time Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    With ActiveChart.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .ChartSize = xlFullPage
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .BlackAndWhite = False
        .Zoom = 100
    End With
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = -0.0035
        .MaximumScale = 0.0035
        .MinorUnit = 0.0005
        .MajorUnit = 0.0005
        .Crosses = xlCustom
        .CrossesAt = -0.0035
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
        False, ShowSeriesName:=False, ShowCategoryName:=True, ShowValue:=False, _
        ShowPercentage:=False, ShowBubbleSize:=False
    ActiveChart.SeriesCollection(1).DataLabels.Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionAbove
        .Orientation = xlUpward
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionBelow
        .Orientation = xlUpward
    End With
    ActiveChart.Axes(xlCategory).Select
    With ActiveChart.Axes(xlCategory)
        .CrossesAt = 1
        .TickLabelSpacing = 2
        .TickMarkSpacing = 1
        .AxisBetweenCategories = True
        .ReversePlotOrder = False
    End With
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = False
        .HasAxis(xlValue, xlPrimary) = True
    End With
    ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
    ActiveChart.SeriesCollection(1).DataLabels.Select
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    Selection.Shadow = False
    With Selection.Interior
        .ColorIndex = 53
        .PatternColorIndex = 1
        .Pattern = xlSolid
    End With
    With Selection.Border
        .Weight = xlHairline
        .LineStyle = xlNone
    End With
    Selection.Shadow = False
    Selection.Interior.ColorIndex = xlNone
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 3
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionCenter
        .Orientation = xlUpward
    End With
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 9
        .Background = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionBelow
        .Orientation = xlUpward
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Position = xlLabelPositionAbove
        .Orientation = xlUpward
    End With
    ActiveChart.Shapes.AddShape(msoShapeRectangle, 48.75, 55.5, 768.75, 27.75). _
        Select
    ActiveChart.Shapes.AddShape(msoShapeRectangle, 48.75, 83.25, 768.75, 55.5). _
        Select
    ActiveChart.Shapes.AddShape(msoShapeRectangle, 48.75, 138.75, 768.75, 221.25). _
        Select
    ActiveChart.Shapes.AddShape(msoShapeRectangle, 48.75, 360#, 768.75, 54.75). _
        Select
    ActiveChart.Shapes.AddShape(msoShapeRectangle, 48.75, 414.75, 768.75, 29.25). _
        Select
    Selection.ShapeRange.IncrementLeft -0.07
    Selection.ShapeRange.IncrementTop 0.06
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    ActiveChart.Shapes("Rectangle 4").Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 34
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 34
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    ActiveChart.Shapes("Rectangle 3").Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 17
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 17
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 17
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    ActiveChart.Shapes("Rectangle 2").Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 34
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 34
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    ActiveChart.Shapes("Rectangle 1").Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Fill.Transparency = 0.7
    Selection.ShapeRange.Line.Weight = 0.75
    Selection.ShapeRange.Line.DashStyle = msoLineSolid
    Selection.ShapeRange.Line.Style = msoLineSingle
    Selection.ShapeRange.Line.Transparency = 0#
    Selection.ShapeRange.Line.Visible = msoTrue
    Selection.ShapeRange.Line.ForeColor.SchemeColor = 10
    Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
    ActiveChart.Shapes.Range(Array("Rectangle 1", "Rectangle 2")).Select
    ActiveChart.Shapes.Range(Array("Rectangle 1", "Rectangle 2", "Rectangle 3")). _
        Select
    ActiveChart.Shapes.Range(Array("Rectangle 1", "Rectangle 2", "Rectangle 3", _
        "Rectangle 4")).Select
    ActiveChart.Shapes.Range(Array("Rectangle 1", "Rectangle 2", "Rectangle 3", _
        "Rectangle 4" _
    , "Rectangle 5")).Select
    Selection.ShapeRange.Group.Select
    Selection.Name = "Range"
    Charfa
End Sub
 
You would always have to state the range, you could use a named range but you would have to alter it for any changes, or you could try

Code: 
Set myrange = ActiveWindow.RangeSelection
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=myrange, PlotBy:= _
        xlColumns

in which you drag your cursor to highlight the range and then run the macro,

please note that this is untried.


Brian
 
Ok thanks I will give it a try
 
You must log in or register to reply here.

Similar threads

C
Divide code to avoid "Procedure too large error"
Replies
18
Views
2,407
The_Doc_Man
The_Doc_Man
P
Export to Excel not working after SetAccessWindow code
Replies
4
Views
1,278
isladogs
isladogs
P
Proper Error Handling
Replies
14
Views
2,193
psyc0tic1
P
C
Run time error '13': Type Mismatch
Replies
5
Views
1,345
The_Doc_Man
The_Doc_Man
P
Export to Excel code tweaking
Replies
13
Views
1,830
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom