Vba chart considering range as continous for some reason ( i.e one range instead of two)

[shakeeb92]

Hello guys,

I'm having difficulties with this piece of code. I have searched the forum, but no luck till now (maybe im not using the right keywords).Here is my problem and thanks in advance.

I am trying to create a chart with VBA that compares the metric (grade percentage) of different people for different months of the year. The trick here is that the number of people to be compared might be different from one run to another. The max number of people to be compared is set to 10. Also, a target on the graph is set using a fixed range.
I have inserted a combobox that enables the user to pick the number of people to be compared before starting the macro in able for the macro to be able to select the right range for the graph. Below is my code which works prfectly except for one instance; when the user only chooses one person to be inserted in the graph . As per the code below, when the user chooses one person, the two ranges to be used should be "H10:H23" and "H24:H37". The problem occurs when the code attempts to change the target range to a line graph. However, for some reason vba can't find the seriescollection (2). This means that the two ranges are considered as one eventhough they are two seperate ranges. Please guide me where have I might gone wrong. Below is a sample table and my my code so far.


Another question I have. Is it possible to include the combobox inside my code ?(maybe in a msg box)

10	Months	A	B	to..	H (person1)	I (person2)	J (person3)	to..	R	S
11	Jan	x	x		90%	89%	45%		x	x
12	feb	x	x		100%	95%	80%		x	x
13	march	x	x		99%	58%	75%		x	x
14	apr	x	x		75%	65%	50%		x	x
15	may	x	x		80%	79%	75%		x	x
...									x	x
to...									x	x
24	target	x	x		93%				x	x
25	target	x	x		93%				x	x
26	target	x	x		93%				x	x
.....		x	x						x	x

<TBODY>
</TBODY>

  Dim GRPerc1 As Range
  Dim GRPerc2 As Range
  Dim GRPerc3 As Range
   
  Sheets("sheet1").Select     ' sheet with data
  x = Range("B42").Value      'combo box is linked to this cell
  Range("H10").Activate        
  Set GRPerc1 = Range("H10:H23", ActiveCell.Offset(0, x - 1))    ' Selects the range of data of the people to be compared based on user selection
  Set  GRPerc2 = Range("H24:H37")   ' Selects the Range of data for the Target
  Set GRPerc3 = Union( GRPerc1,  GRPerc2)
      
Sheets("Graph").Select  ' Graph will be added on this list
On Error Resume Next
ActiveSheet.ChartObjects.Delete
On Error GoTo 0
    
   Charts.Add.Location Where:=xlLocationAsObject, Name:="Graph"
    ActiveChart.ChartType = xlColumnClustered
 ActiveChart.ApplyLayout (5)
    ActiveChart.SetSourceData Source:=GRPerc3
    ActiveChart.SeriesCollection(1).XValues = "=sheet1!$A$11:$A$23"       'X-axis labels i.e months of the year
     
     ActiveChart.SeriesCollection(x + 1).ChartType = xlLine      ' change target to line graph instead of bar
  ActiveChart.SeriesCollection(x + 1).Select
   
With Selection.Format.Line
        .Visible = msoTrue
        .Weight = 5
        End With
  
With ActiveChart
   .Axes(xlValue).MaximumScale = 1
   .HasTitle = False
    .HasTitle = True
      .ChartTitle.Characters.Text = ActiveSheet.Name   ' Chart title
   End With
    
    With ActiveChart.Parent
     .Height = 400 ' resize
     .Width = 800  ' resize
     .Top = 100    ' reposition
     .Left = 50 ' reposition
     End With

Thanks tons

 

[Jon Peltier]

Union(H10:H23,H24:H37) is simply H10:H37. Since it's a single column with no gap in between, Excel merges it into one column. I'm surprised this Union didn't also mess up when GRPerc1 was more than one column.

Probably it's too late now, but it would have made a lot of sense to put the target parallel to all of the results (i.e., another column in rows 10:23).

You don't need to use Set Source Data for all of your series, however. Here's what I would do.

I'd define GRPerc1 and use it with SetSourceData to add all the individual person data to the chart, almost as you've done. Then I'd do this:

GRPerc2 is already defined, so I'll add a new series and use it for the Y values:

With ActiveChart.SeriesCollection.NewSeries
  .Values = GRPerc2
  .Name = "Target"
End With

 

[shakeeb92]

Union(H10:H23,H24:H37) is simply H10:H37. Since it's a single column with no gap in between, Excel merges it into one column. I'm surprised this Union didn't also mess up when GRPerc1 was more than one column.

Probably it's too late now, but it would have made a lot of sense to put the target parallel to all of the results (i.e., another column in rows 10:23).

You don't need to use Set Source Data for all of your series, however. Here's what I would do.

I'd define GRPerc1 and use it with SetSourceData to add all the individual person data to the chart, almost as you've done. Then I'd do this:

GRPerc2 is already defined, so I'll add a new series and use it for the Y values:

With ActiveChart.SeriesCollection.NewSeries
  .Values = GRPerc2
  .Name = "Target"
End With

Jon,

Thanks for your reply. I have tried the Union command multiple times before using it and it seems to work fine (it does not select everything between the ranges). I did this by adding a select command so i can visually see it.

As for your proposed solution, it seems that its a very possible alternative. If I use your method of adding a new series, would this series be counted as seriescollection(2). As you know i will still have to refer to this series beacause i need to change the format of it.

 

[Jon Peltier]

The new series would be the next one after the block using GRPerc1. So if GRPerc1 was only H10:H23, the new series would be series 2. If GRPerc1 was H10:J23 (3 columns), the new series would be series 4.

 

[shakeeb92]

The new series would be the next one after the block using GRPerc1. So if GRPerc1 was only H10:H23, the new series would be series 2. If GRPerc1 was H10:J23 (3 columns), the new series would be series 4.

Working perfectly Now; Issue RESOLVED. Thanks