Bubble Sizes In Bubble-Charts Macro

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


ADVERTISEMENT

Label Bubbles In Bubble Charts

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

Creating Bubble Charts On Three Different Columns Of Data

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

Quadrant Bubble Chart

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

Formatting Axis Of Bubble Chart

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

Conditional Formatting In Bubble Chart

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

Plotting Cities On Bubble Chart?

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

Automate New Series For Bubble Chart

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

Adding New Series To Bubble Chart

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

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 View Related

Bubble Chart: Add To Label Hover Display

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

Bubble Sort: Comparing Numbers & Letters

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

Change Bubble Chart Color Based On Cell Value

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

Dynamic Bubble Chart - Data Point Naming

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

Conditional Formatting Of Bubble Chart Points Based On Bubblesizes

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

Bubble Chart Function - Click To Open Percentage By Subject Line

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

Combine (Overlay) Bubble Chart & Point Scatter Chart

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

Convert Scatter Chart To Bubble Chart

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

Macro For Name Generation From Product To Product With Sizes And Filenames

Jan 30, 2014

I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.

View 4 Replies View Related

Macro To Export Charts As PNG

Aug 14, 2014

I am new to macros and I am trying to export 20 charts that are one worksheet. I found the code below online and it it does work for a majority of the charts. However, it randomly skips some of the charts and does not export the. I receive an Run Time error 76; Path not found. Each chart is named.

Sub Create_Png()
Dim objCht As ChartObject
Dim strPath As String
strPath = "C:Path Name"

For Each objCht In ActiveSheet.ChartObjects
objCht.Chart.Export strPath & objCht.Name & ".png", FilterName:="png"
Next
End Su

View 1 Replies View Related

VBA Macro To Hide Charts?

Jan 5, 2012

I have the following piece of code to hide all worksheets in my workbook but modify it to hide the multiple charts I still have open after running this:

Code:
Sub HideAllSheets()
Application.ScreenUpdating = False
Dim ws As Worksheet

[Code].....

View 3 Replies View Related

Converting Static Charts To Dynamic Charts

Jun 27, 2013

My DB is in table format . I use this table as source data for 2 barcharts and 1 pie chart.Following are my table headers

Costs|exp heads|Month1|Month2|...|Month n|Spark lines|Average

When i add a month coloumn,Sparklines and Average coloumn should get updated automaticaly.Now this is not happening even if the data is in table format.I also want the graphs to be automaticaly updated.

View 2 Replies View Related

VBA Macro To Create New Worksheet, Charts, Etc.

Oct 23, 2008

I need a macro to create the following worksheets and charts from an Excel data set:

Three (3) worksheets (already created manually in attached Excel file):

1. Chart Data.
(a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr).
(b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset.

2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data).

3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data)

At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.

View 14 Replies View Related

Creating Many Excel Charts With A Macro?

Oct 21, 2011

I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.

Sample Data:

Code:
State Characteristic Value07 Value08 Value09
Alaska A 11 23 45
Alaska B 13 22 98
Alaska C 99 91 21
Alabama A 23 14 11
Alabama B 44 62 76
Alabama C 75 47 21[code]....

This code doesn't run at all.

View 4 Replies View Related

How To Plot Multiple Charts Using Macro

Jul 6, 2012

I would like to generate a macro which can work on by importing an excel tabulated datas and plot it into several graphs in one click and added with a filter function would be best.

Here i link one of the sample of macro for this :

[URL]

View 5 Replies View Related

Macro To AutoScale Charts By Chart Name?

Aug 28, 2012

I have a code for auto scaling charts, but I'm looking for a slight tweak to improve its performance. Here is the code:

Public Sub ChartScale()With ActiveChart.Axes(xlValue).MinimumScale = Range("J152").MaximumScale = Range("J153")End WithEnd Sub
(where J152 and J153 are the cells being used to scale the chart)

What happens is that I create a macro button, and then have to select a specific chart, then press the button to scale it. I have 2 main questions I'm trying to have answered to improve this process:

1) Is there a way to make this process automatic (i.e. no button press required)? The chart would autoscale automatically upon the min and max number changing.

2) And more importantly, can I specify the macro to an individual chart instead of the active one? For example, I'd like 4 charts on one sheet to all have their own auto scaling being performed. So, in the above code, the chart uses cells J152 and J153 for the scaling. I'd like the others to use other cells for the scaling. Cells J152 and J153 would auto scale chart "Name 1", cells K152 and K153 would autoscale Chart "Name 2", and so forth for 4 charts.

View 2 Replies View Related

Excel Macro For Charts In PIE Chart?

Jan 28, 2013

i am creating a macro to animate the charts, but i need a code where, if i select a block in the chart it should take the point name automatically.

View 1 Replies View Related

Macro To Create Dynamic Charts

Dec 22, 2006

i want to create dynamic Excel Charts in one sheet ,The sheeet contain dynamic columns and rows and i want to create for each 4 columns a chart in a new sheet ,i started to try it in VBA and didnt founs the way to select 4 columns as a range, i succeseed to do it with msgboox that the user enter the range but i want to do it in a loop

View 9 Replies View Related

Row Height And Font Sizes

Feb 21, 2014

I am building up a formatted invoice, line by line all of which is working well from the data point of view but no quite so well from the presentation point.

My text is all in Arial 12pt and I have the row height set to "Auto" and cell alignment to wrap text (at least one cell per row often has 3-4 lines of text so I need "text wrap" on)

My issue is that the printed output from a multi row invoice looks "crowded" and as this is the view the client gets to see (and hopefully pay ) the look is important. I tried to set the row heights to say .71cm but that causes the text wrap to stop working correctly.

I think I am trying to get some form of "inside top" and "inside bottom" adjustment / fill / padding (which is what I would do if I was using some form of documentation tool like Madcap Flare for example) but I cannot see how to do it in excel.

View 9 Replies View Related

VBA Macro Code To Create Multiple Charts

May 9, 2009

I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:

Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveWindow.SmallScroll Down:=-3
End Sub

How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved