Multiple Non Adjacent Ranges For Chart

angshuman · Aug 22nd 2007

Hi,

I am generating a chart through a macro. I am using 4 non adjacent range and the end point of the range is represented by a variable as the end point is not fixed. I am using following code.

Code

Row = 5


StartPoint1 = "C19"
EndPoint1 = "C" & 19 + Row
    
StartPoint2 = "E19"
EndPoint2 = "E" & 19 + Row
     
StartPoint3 = "G19"
EndPoint3 = "G" & 19 + Row
 
StartPoint4 = "I19"
EndPoint4 = "I" & 19 + Row


ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
        "StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4"), PlotBy:=xlColumns

Display More

Can any one please help to rectify the error. Your help is highly appreciated.

Regards

Angshuman

Mavyak · Aug 22nd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

Code

Source:= Sheets("Summary").Range( _ 
"StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4")

Should probably be:

Code

Source:= Sheets("Summary").Range( _ 
StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)

You can't put your variables in the quotes or VB treats them as literals. You have to concatenqate them with the ampersand symbol.

hth,

Mav

angshuman · Aug 22nd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

Hi ,

I changed as per your advice and it worked. however there are two more lines in my code which are as below and needs some changes.

Code

ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
    StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)


ActiveChart.SeriesCollection(4).Name = "=Summary!R17C7"
ActiveChart.SeriesCollection(4).XValues = "=Summary!R19C2:R23C2"

In the above code I need to change the range of the SeriesCollection(4).Xvalues with variables.

Please help

Thanks and Regards

Angshuman

p45cal · Aug 22nd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

Try changing:

Code

ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _ 
"StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4"), PlotBy:=xlColumns

to

Code

ActiveChart.SetSourceData Source:=Sheets("Summary").Range(StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4), PlotBy:=xlColumns

or reduce the number of '&'s in the final line with

Code

Sub blah2()
Row = 5
 
StartPoint1 = "C19:"
EndPoint1 = "C" & 19 + Row
 
StartPoint2 = ",E19:"
EndPoint2 = "E" & 19 + Row
 
StartPoint3 = ",G19:"
EndPoint3 = "G" & 19 + Row
 
StartPoint4 = ",I19:"
EndPoint4 = "I" & 19 + Row
 
ActiveChart.SetSourceData Source:=Sheets("Summary").Range(StartPoint1 & EndPoint1 & StartPoint2 & EndPoint2 & StartPoint3 & EndPoint3 & StartPoint4 & EndPoint4), PlotBy:=xlColumns
End Sub

Display More

but it may be easier to:

Code

Sub blah2()
myRow = 5
With Sheets("Summary")
 Set myRng1 = .Range("C19").Resize(myRow)
 Set myRng2 = .Range("E19").Resize(myRow)
 Set myRng3 = .Range("G19").Resize(myRow)
 Set myRng4 = .Range("I19").Resize(myRow)
End With
ActiveChart.SetSourceData Source:=Union(myRng1, myRng2, myRng3, myRng4), PlotBy:=xlColumns
End Sub

p45cal
ps not fully tested (all except for setting up a chart (the ranges have been verified))
pps I changed Row to myRow since 'Row' is a reserved word (or some such!).

angshuman · Aug 22nd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

Hi p4,

It gives Copile Error "Object Required'..Please advise

Thanks and Rehards

Angshuman

p45cal · Aug 22nd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

What macro gives the error, and which line (if applicable)?

angshuman · Aug 23rd 2007

Re: Select Multiple Non Adjacent Range For Chart With Variables

Hi,

Following are the codes I am using now. The code i working fine for the first graph. however for the next graph it gives error "Run -time error '1004'; Application-definded or Object-defined error"..Please help.

Code

Sub Chart()


'===============================================================================
'Chart : No Of Issues
'================================================================================
    
    
   Dim MyRow As Long
   
      Sheets("Summary").Select
    Cells(60, 2).Select
        
    With ActiveSheet
        MyRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    MyRow = MyRow - 60
    
    StartPoint1 = "C19"
    EndPoint1 = "C" & (19 + MyRow)
     
    StartPoint2 = "E19"
    EndPoint2 = "E" & (19 + MyRow)
     
    StartPoint3 = "G19"
    EndPoint3 = "G" & (19 + MyRow)
     
    StartPoint4 = "I19"
    EndPoint4 = "I" & (19 + MyRow)
    
    StartPoint5 = "B19"
    EndPoint5 = "B" & (19 + MyRow)


    
    
    Sheets("Chart").Select
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
    StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)
    
    
    ActiveChart.ChartType = xl3DColumnClustered
    
    ActiveChart.SeriesCollection(1).XValues = Sheets("Summary").Range( _
    StartPoint1 & ":" & EndPoint1)
    ActiveChart.SeriesCollection(1).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(1).Name = "=Summary!R17C3"
    
    ActiveChart.SeriesCollection(2).XValues = Sheets("Summary").Range( _
    StartPoint2 & ":" & EndPoint2)
    ActiveChart.SeriesCollection(2).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(2).Name = "=Summary!R17C5"
    
    ActiveChart.SeriesCollection(3).XValues = Sheets("Summary").Range( _
    StartPoint3 & ":" & EndPoint3)
    ActiveChart.SeriesCollection(3).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(3).Name = "=Summary!R17C7"
    
    ActiveChart.SeriesCollection(4).XValues = Sheets("Summary").Range( _
    StartPoint4 & ":" & EndPoint4)
    ActiveChart.SeriesCollection(4).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(4).Name = "=Summary!R17C9"
    
        
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
    
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    
    ActiveChart.Parent.Name = "Issue Count"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Issue Analysis"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "No Of Issues"
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With


    
    ActiveChart.Walls.Select
    With ActiveChart
        .Elevation = 30
        .Perspective = 30
        .Rotation = 20
        .RightAngleAxes = True
        .HeightPercent = 100
        .AutoScaling = True
    End With
        
    With ActiveSheet.Shapes("Issue Count")
        .IncrementLeft -169.5
        .IncrementTop -88.5
        .ScaleWidth 1.47, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.41, msoFalse, msoScaleFromTopLeft
    End With
    
    ActiveSheet.ChartObjects("Issue Count").Activate
    ActiveChart.Walls.Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.PresetTextured PresetTexture:=msoTextureParchment
    Selection.Fill.Visible = True
    ActiveChart.Floor.Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.PresetTextured PresetTexture:=msoTextureParchment
    Selection.Fill.Visible = True
    
    
    ActiveSheet.ChartObjects("Issue Count").Activate
    ActiveChart.ChartArea.Select
    With Selection.Border
        .Weight = 2
        .LineStyle = -1
    End With
    Sheets("Chart").DrawingObjects("Issue Count").RoundedCorners = True
    Sheets("Chart").DrawingObjects("Issue Count").Shadow = True
    Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _
        Degree:=0.231372549019608
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 58
    End With
    
     
   '===========================================================
   'Chart : Average Response Time'
   '===========================================================
          
    Sheets("Summary").Select
    Cells(60, 2).Select
        
    With ActiveSheet
        MyRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With
    
    MyRow = MyRow - 60
    
    StartPoint1 = "D19"
    EndPoint1 = "D" & (19 + MyRow)
     
    StartPoint2 = "F19"
    EndPoint2 = "F" & (19 + MyRow)
     
    StartPoint3 = "H19"
    EndPoint3 = "H" & (19 + MyRow)
     
    StartPoint4 = "J19"
    EndPoint4 = "J" & (19 + MyRow)
    
    StartPoint5 = "B19"
    EndPoint5 = "B" & (19 + MyRow)


    
    
    Sheets("Chart").Select
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
    StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)
    
    
    ActiveChart.ChartType = xl3DColumnClustered
    
    ActiveChart.SeriesCollection(1).XValues = Sheets("Summary").Range( _
    StartPoint1 & ":" & EndPoint1)
    ActiveChart.SeriesCollection(1).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(1).Name = "=Summary!R17C3"
    
    ActiveChart.SeriesCollection(2).XValues = Sheets("Summary").Range( _
    StartPoint2 & ":" & EndPoint2)
    ActiveChart.SeriesCollection(2).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(2).Name = "=Summary!R17C5"
    
    ActiveChart.SeriesCollection(3).XValues = Sheets("Summary").Range( _
    StartPoint3 & ":" & EndPoint3)
    ActiveChart.SeriesCollection(3).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(3).Name = "=Summary!R17C7"
    
    ActiveChart.SeriesCollection(4).XValues = Sheets("Summary").Range( _
    StartPoint4 & ":" & EndPoint4)
    ActiveChart.SeriesCollection(4).XValues = Sheets("Summary").Range( _
    StartPoint5 & ":" & EndPoint5)
    ActiveChart.SeriesCollection(4).Name = "=Summary!R17C9"
    
        
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Chart"
    
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlBottom
    
    ActiveChart.Parent.Name = "Response Time"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Average Response Time"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Characters.Text = "Time In HH:MM:SS"
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .Orientation = xlUpward
    End With


    
    ActiveChart.Walls.Select
    With ActiveChart
        .Elevation = 30
        .Perspective = 30
        .Rotation = 20
        .RightAngleAxes = True
        .HeightPercent = 100
        .AutoScaling = True
    End With
        
    With ActiveSheet.Shapes("Response Time")
        .IncrementLeft -169.5
        .IncrementTop -88.5
        .ScaleWidth 1.47, msoFalse, msoScaleFromTopLeft
        .ScaleHeight 1.41, msoFalse, msoScaleFromTopLeft
    End With
    
    ActiveSheet.ChartObjects("Response Time").Activate
    ActiveChart.Walls.Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.PresetTextured PresetTexture:=msoTextureParchment
    Selection.Fill.Visible = True
    ActiveChart.Floor.Select
    With Selection.Border
        .ColorIndex = 57
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    Selection.Fill.PresetTextured PresetTexture:=msoTextureParchment
    Selection.Fill.Visible = True
    
    
    ActiveSheet.ChartObjects("Response Time").Activate
    ActiveChart.ChartArea.Select
    With Selection.Border
        .Weight = 2
        .LineStyle = -1
    End With
    Sheets("Chart").DrawingObjects("Response Time").RoundedCorners = True
    Sheets("Chart").DrawingObjects("Response Time").Shadow = True
    Selection.Fill.OneColorGradient Style:=msoGradientHorizontal, Variant:=1, _
        Degree:=0.231372549019608
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 58
    End With
    
    
End Sub

Display More

Thanks and Regards,

Angshuman

**Andy Pope** · Aug 23rd 2007

Re: Multiple Non Adjacent Ranges For Chart

Can you post an example workbook of your data, use random numbers is work sensitive.

My test of your code works and creates two charts.

angshuman · Aug 23rd 2007

Re: Multiple Non Adjacent Ranges For Chart

Hi Andy,

As per my understanding, it is because when I the second chart is being created, it is also taking the old data range. Can you please tell me the code with whcihc I can delete the old data range that was used for the first chart.

Thanks and Regards

Angshuman

**Andy Pope** · Aug 23rd 2007

Re: Multiple Non Adjacent Ranges For Chart

Not the case in my test. Each chart is based on different non adjacent ranges.

I think a test dataset would help. At least I would be able to generate the problem you see.

Multiple Non Adjacent Ranges For Chart

Participate now!

Share