Bubble Graph Code: SeriesCollection.NewSeries
Sep 4, 2006
I'm trying to make a macro that generates a bubble graph from a defined range. I however can't seem to get the SeriesCollection.NewSeries to work.
Dim nr As Integer
Dim myChart As ChartObject
Set myChart = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
myChart.Chart.SetSourceData Source:= Sheets("Sheet1").range("A2:D2"), PlotBy:= _
xlRows
myChart.Activate
With ActiveChart.SeriesCollection.NewSeries
.Name = ActiveSheet.Cells(nr + 1, A)
.XValues = ActiveSheet.Cells(nr + 1, B)
.Values = ActiveSheet.Cells(nr + 1, C)
.BubbleSizes = ActiveSheet.Cells(nr + 1, D)
With .Interior
If Cells(nr + 1, G) >= 50 Then
.ColorIndex = 4
Else
.ColorIndex = 10
End If
End With
End With
myChart.Chart.ChartType = xlBubble
View 6 Replies
ADVERTISEMENT
Nov 9, 2006
Im trying to create a simple 3D Bubble Chart macro, and as most people who've never dealt with this before (i think), i've hit a wall with BubbleSizes. I've attached the file im trying to get to work. The VBA is quite simple but an error occurs at BubbleSizes. I've read that BubbleSizes takes a different reference style than xValues and such, but haven't had any luck trying to change that. For those who just want to see the code without getting the file:
Sub EmbeddedChartFromScratch()
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim iColumn As Long
' make sure a range is selected
If TypeName(Selection) <> "Range" Then Exit Sub
' define chart data
Set rngChtData = Selection
' add the chart
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=250, Width:=375, Top:=75, Height:=225)
With myChtObj.Chart................................
View 5 Replies
View Related
Jun 22, 2006
Is there anyone out there who've used SeriesCollection.Values before? As I know, there's no way to do double indexing for the values of the point referencing to. So something like the following would not be possible.
CCArray(i) = ActiveChart.SeriesCollection(i).Values
CCArray(i, j) = ActiveChart.SeriesCollection(i).Values(j)
CCArray(I, j) = ActiveChart.SeriesCollection(i).Values
But I need to get the values of the points by some means. Is there anyone who could help me find out the best way for me to put the values into a doubly for...next loop. The following is the code that has the idea of what I'm trying to achieve but , of course, the syntax does not comply to what VB allows.
Redim CC_Array(1 To SeriesCount, 1 To 13) As Integer
For i = 1 To SeriesCount
For j = 1 To 13
CC_Array(i, j) = ActiveChart.SeriesCollection(i).Values(j)
Next j
Next i
View 3 Replies
View Related
Aug 16, 2007
I am trying to generate a series of charts. Each row (and 3 columns) is the sole series of each chart, but am having trouble setting the values for the chart seriescollections. I'm using the following
Sub DrawCharts()
Dim Ws As Worksheet
Dim NewWs As Worksheet
Dim cht As Chart
Dim LastRow As Long
Dim CurrRow As Long
Dim PointRow As Long
Set Ws = ThisWorkbook.Worksheets("Global Summary")
LastRow = 6
For CurrRow = 5 To LastRow
Set NewWs = ThisWorkbook.Worksheets.Add
NewWs. Name = Ws.Range("A" & CurrRow).Value
Set cht = ThisWorkbook.Charts.Add
With cht
.ChartType = xlBarClustered..................
View 4 Replies
View Related
Apr 7, 2007
Series are added to a chart on a chartsheet in WB Retirement.xls with the following macro:
Sub AddMyWife()
Application. ScreenUpdating = False
Sheets("Chart").Select
With ActiveChart.SeriesCollection.NewSeries
. Name = "MyWife"
.Values = "='Retirement.xls'!MyWife"
End With
ActiveChart.SeriesCollection("MyWife").Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerStyle = xlDiamond
.MarkerSize = 3
.MarkerForegroundColorIndex = 3
.MarkerBackgroundColorIndex = 3
End With
Application.ScreenUpdating = True
ActiveChart.Deselect
End Sub
The series is deleted with:
Sub DeleteMyWife()
Sheets("Chart").Select
On Error Resume Next
ActiveChart.SeriesCollection("MyWife").Delete
ActiveChart.Deselect
End Sub...
View 4 Replies
View Related
Apr 20, 2014
How to see if a SeriesCollection that has been named exists so when the corresponding toggle button it clicked it turns on or off the correct corresponding dataseries.
When I create a series I use something like:
[Code] ............
Where the range "tblJim[Quizes]" contains the quiz scores which are the data series pointes and "=Data!$C$6:$C$35" has the respective dates.
When that toggle button is clicked I need to test if the SeriesCollection "Jim" exists so if it does I can delete it and if not, create it.
Also, as much as I hate to multi-subject but usually do anyway, I am wondering if there is good example of doing the same ends by a different strategy - a chart with everyone on it and by applying data filters to the dataset I would turn on and off people's data.
View 2 Replies
View Related
Jun 1, 2006
I am trying to create a chart that will Add or Remove data entries as toggle buttons are clicked. I've fallen at the first hurdle. When I remove one series from the source data, all the SeriesCollection numbers will change down. e.g. ->
ActiveChart.SeriesCollection(2).Delete
where (2) will change to (1) when I delete a series. This then screws up subsequent Removal macros. Is there any way I can either lock the SeriesCollection number?
View 2 Replies
View Related
Nov 7, 2006
I try to plot an XY graph with a VB macro but I don't manage to select the right name of the active sheet.
Sub plot()
WksName = ActiveSheet.Name
Worksheets(WksName).Activate
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets(WksName).Range("A11:F12"), PlotBy:= _
xlRows
ActiveChart.SeriesCollection(1).XValues = "=WksName!R5C8:R643C8"
ActiveChart.SeriesCollection(1).Values = "=WksName!R5C9:R643C9"
ActiveChart.SeriesCollection(1).Name = "=""Specular"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=WksName
End Sub
View 6 Replies
View Related
Mar 30, 2012
I must graph a series of data points in an X-Y scatter point chart multiple times, then fit every type of trendline to that a seperate graph. Using VBA, I must then extract the R Sqaured value from each trendline, and find the best trendline suitable for the job.
How to extract the R Sqaured value from the chart though.
View 5 Replies
View Related
Jul 22, 2014
I need a hand with a worksheet that we use.
We do a penetration test on soil, write down the values from the machine, then enter into excel and it plots a line graph.
Depending on the values, sometimes a correction is required. This is manually done at the moment.
I would like to have excel do it for us.
I have attached an example of a manual correction I have done as well as the excel calculation worksheet.
There are 2 results given (one at 2.5mm penetration & one at 5.0mm penetration), we calculate both, then use the highest result for the report.
the x value is a constant and the y is a variable.
Attached Files :
Copy of Master WA CBR Worksheet Soaked.xls‎
DOC230714-002.pdf‎
View 14 Replies
View Related
May 2, 2014
I have a macro code that will create line graph referring the data given in defined column A1-C4,
Code with Static column range:-
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$4")
ActiveChart.ChartType = xlLineStacked
I tried to modify the above code, so it will refer undefined/dynamic data column, but getting an error during execution "Run Time Error - 424:" "Object required"
Code with Dynamic column range:-
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ActiveChart.ChartType = xlLineStacked
View 1 Replies
View Related
Sep 27, 2007
create a Quadrant Bubble chart. I have found examples of using scatter charts but unfortantely those charts do not satisfy what I am trying to accomplish.
View 5 Replies
View Related
Aug 2, 2013
I created a bubble chart whose x-axis and y-axis show every 10th value between 0 and 100 (i.e. 0, 10, 20, 30...). The axes intersect at (50, 50) to create 4 quadrants.
I want to erase/hide the values on the axes (i.e. the axes will no longer show 0, 10, 20, 30... and at the same time, the bubbles will still be in the same place) to create 4 blank quadrants and make my bubble chart easier to look at.
View 4 Replies
View Related
Nov 26, 2013
I have the table: chart1.png
If the bubble size is 0-5, I need the bubble color to be green
If the bubble size is 6-15, I need the bubble color to be yellow
If the bubble size is 16-25, I need the bubble color to be red.
So, right now my chart looks like this: chart.png
As you can see the top bubble is the right color, but based on the bottom bubble value, it should be green.
How do I make this change colors dynamically based on the bubble size value?
View 1 Replies
View Related
Apr 10, 2012
I have the Lat and Long for each of several US cities. I want to use these to create a bubble chart that plots the cities and which I can place over a US map picture.
However, I have the problem that, because of the curvature of the earth, the city locations form the lat and longs are not tying with the map.
how to adjust the lat and longs so that they will correspond to the typical US map?
For example, the base lat and longs for Denver are 39.5742 and -104.8588. What formulas can I use to modify these to match the standard map projection?
View 9 Replies
View Related
Aug 11, 2006
I have a file in which column a shows product names, and columns b and c contains quantitative data. I want to put this in a bubble graph (column b = y axis) and (column c = x axis) and label the bubble with the product names. I tried to make this graph but either it doesn't show labels ,either it doesn't provide bubbles. How can i label the bubbles? I attached a small sample file.
View 4 Replies
View Related
Apr 15, 2007
I’m trying to take an existing bubble chart, paste new data into the sheet,
then march sequentially from row to row, adding specific cells within each as a new series in the chart until I run out of rows. Below is my latest attempt (not working of course).
Sub setseries1()
Range("A1").Select 'select upper left cell to start
Do Until ActiveCell.Value = "" 'stop when you run out of rows
ActiveChart.ChartArea.Select
ActiveChart.ChartType = xlBubble
With ActiveChart.SeriesCollection.NewSeries
'use these values for the next series
.Name = ActiveCell.Offset(1, 0).Select
.BubbleSizes = ActiveCell.Offset(0, 1).Select
.Values = ActiveCell.Offset(0, 1).Select
.XValues = ActiveCell.Offset(0, 9).Select
End With
'go to next row, and repeat
ActiveCell = ActiveCell.Offset(0, -1)
Loop
End Sub
View 7 Replies
View Related
Jul 12, 2007
I have a worksheet where I am collecting data and a seperat Chart. The first part of this code which adds a new row in my table works perfectly fine, but the latter part (based on a macro recording) does not. I need to specify the relevant row for the new series in the bubble chart. The colums are of course the same.
Private Sub CommandButton1_Click()
Dim LastRow, LastRef As Long
'Works!
ThisWorkbook.Worksheets("Projektradar - input").Select
Range("Q3").Activate
LastRef = ActiveCell.Value
Range("Q3").Value = LastRef + 1
View 4 Replies
View Related
May 23, 2012
I'm trying to create a bubble chart created on three different columns of data. I'd like the following:
-The X-axis should just show the first columns of dates I have stored.
-The Y-axis should be the number of lots of a product that I bought of a certain product.
-I'd like the actual bubbles to not only represent the number of lots purchased on the given date, but I'd like the size to be represented by the third column, which is the average price of each lot.
Is there a way to do this? I've been trying to maneuver it around all morning, and can't seem to get it how I want it. I'm not sure if I'm ordering the columns incorrectly or what.
View 3 Replies
View Related
Dec 19, 2006
When you hold your mouse over a bubble on a bubble chart, it typically displays the x value, y value and size value. Is there any way to add other captions to display? For example, if my chart is based on sales data and I'm plotting a point based on ease of probability (x axis) and dollar value (y value)....I'd also like to see the initials of the salesperson assigned to that prospect when I hover over the bubble?
View 2 Replies
View Related
Jun 3, 2007
I have 3 departments, each with a value. I want to sort from lowest value to greatest (which I have done) but some departments won't have a value and therefore will have "n/a" in the place of the value. When sorting, "n/a" always comes out as the greatest value but I want "n/a" to be the lowest value - since it means there is no value.
Here is an example of the data:
Depts: Value:
580 15.75
558 19.01
538 n/a
Here is the code (sorting is being done on the value obviously, and the switching of the Depts to stay with the value is also done in the code)
Private Sub RankPerformance()
Dim bytValuesArrayCount As Byte
Dim A As Byte
Dim B As Byte
Dim vTemp As Variant 'must be type since value can be number or string ("n/a")
bytValuesArrayCount = UBound(ValuesArray)
The only way I know to do it is to sort using the above code, then do another type of sort if a value is not numeric then it is placed at the end...but I'm trying to make the code as efficient as possible
View 5 Replies
View Related
Aug 30, 2008
Let Sheet 1 have data in 3 columns to support a standard bubble chart
Col A: X Values
Col B: Y Values
Col C: Bubble Size
Id like to be able to color the bubbles according to the Y values, whether the Y values fall within the following ranges
Green for Y <=2
Orange for 2<Y<=5
Red for Y > 5
Im assuming some straightforward VB code is in the works but Im not quite there yet
View 4 Replies
View Related
Feb 16, 2007
I am looking to create a dynamic bubble chart. To do this I am using offset to create the series formula which without listing all the data names turns out to be something like this (where 297 will adjust to the number of rows with data): =SERIES(Project!$A$5:$A$297, Project!$J$5:$J$297,Project!$P$5:$P$297,2,Project!$Q$5:$Q$297)
My problem is that by creating the chart in this fassion I am unable to get a unique name for each data point. For example, if row A looks like this,
A5: 1
A6: 2
A7: 3
each data point will be named 1 2 3. How do I either create a new series for each row dynamically or get the specific name from column A to associate with the correct data point?
View 2 Replies
View Related
Jul 26, 2012
I am trying to format the colors of the bubbles on my bubble chart to Green (>5), Yellow (4-5), or Red (<4) based on the value used to create the bubblesize.
The problem that I am having is that .BubbleSizes is property of type String, not range like .xValues, or .Values. So instead of pulling in the value, I am pulling in the reference and getting a "Type Mismatch" Error. The reference of my data for .BubbleSizes is $D$5:$D51. Each row is it's own series and the list is dynamic, so I need to be able to support future rows without a lot of maintenance.
Here is the code that I have so far.
VB:
Sub DataSeriesFormat()
'
' DataSeriesFormat Macro
' Format Data Series based on defined parameters for Green, Yellow, and Red. Created by Derek Steinmetz 7/24/12
'
Dim x As Integer
Dim val As Variant
Dim Green As Range
[Code] ......
View 1 Replies
View Related
May 23, 2014
I am new to excel and to the bubble chart function and need creating one for a presentation.
I was asked to do a bubble chart to show the open and click to open percentage by subject line
E.g.
Subject line: Win 20% open, 23 % Click to Open, Getaway 20$ Open, 21% CTO etc., to include an industry average for open and CTO in the chart.
X axis: click to open and Y axis: open
I tried the following in the picture and it doesn't look right.
Capture.JPG
View 1 Replies
View Related
Mar 21, 2014
I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
View 7 Replies
View Related
Jun 11, 2006
How can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500
1/3/2000 $600
3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
View 2 Replies
View Related
Jun 30, 2009
I would like combine (overlay) a bubble chart with a connected point scatter chart. I understand that, without VBA, this is not possible. However, I understand that, by using VBA, the markers of a scatter chart can be configured as circles with their size proportional to values in a specified column. This pseudo-bubble chart can then easily be combined with a connected point scatter chart.
My question is: does anyone have any VBA code to share that shows how to configure a scatter chart as the type of pseudo-bubble chart described above.
View 2 Replies
View Related
Dec 30, 2008
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value.
A short example:
Imagine you own 3 different stores and you're selling oranges. So your table looks like this:
http://img179.imageshack.us/my.php?image=orangeshm4.jpg
Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source):
Date on the x axis,
Oranges sold on the y axis,
and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
View 2 Replies
View Related
Jun 28, 2009
I would like to combine / overlay a bubble chart and scatter chart with straight connectors.
I understand that, without VBA, it is not possible to combine a bubble chart with a scatter chart.
Unfortunately, I am a VBA newbie, and so I cannot write my own code (though I can usually adapt code to my specific environment).
My question is: does anyone have VBA code to share that will convert a scatter chart series (x,y data in 2 columns) to a bubble-style chart (bubble radius in 3rd column)?
View 2 Replies
View Related
