Chart Loop To Change Axes Dynamically

Oct 11, 2006

I've got an excel worksheet that contains charts 5-14, each with different max axes on primary and secondary scales. Right now the code below works to adjust the axes, however I have pasted this into a script 10 times, once for each chart with different references to the chart number, the max primary axis and max secondary axis. how I could write the following as a loop so that I don't need to have to repeat this routine in my code?

Sub ChartsBillingsShare()
ActiveSheet.ChartObjects("Chart 5").Activate
With ActiveChart
.HasAxis(xlValue, xlPrimary) = True
.HasAxis(xlValue, xlSecondary) = True
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = Range("Y6").Value
End With.................................

View 4 Replies


ADVERTISEMENT

Change Chart Range Dynamically

Sep 24, 2006

I maintain Production data in an Excel Sheet and have the code which will create Chart for each Employee. Sample Sheet Attached. However, I want the Chart Range to be updated in a Single Chart and exported to a .Gif File.

View 2 Replies View Related

Dynamically Change How Values Are Displayed In A Chart

Dec 23, 2013

Is it possible to dynamically change how values are displayed in charts?

I have a list which references different named ranges. It either needs to display the values in a graph as a number (without decimals) or a percentage depending on the source. Is this possible? Maybe with some VBA? Or am I asking too much from excel?

View 4 Replies View Related

Vba Chart Axes Editing Syntax

Jul 12, 2007

my VBA code does some simple editing. Something I would like it to do is make the x-axis and y-axis lines be editable. What I mean is say for editing the Plot area background you would use this

ActiveChart.PlotArea.Format.Line.ForeColor.RGB = RGB(0, 0, 0)

I am looking for the equivalent to do the x and y axis lines.

View 4 Replies View Related

Data Series On A Line Column 2 Axes Chart

Feb 15, 2007

Im plotting data on a Line-Column 2 Axes Chart which works really well with 4 data series (2 column, 2 Line) but when adding a fifth data series, the fourth disappears from view. I can see its still there cos I can display the values but I cant get the line to appear. The values for the 5th series are also similar to other series values so its not like the chart cant display it....are there limits to the number of series on such a chart?

View 9 Replies View Related

Merging Code For Hiding Columns And Setting Chart Axes Values

Feb 23, 2010

I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.

View 2 Replies View Related

Dynamically Chart Series

Sep 18, 2008

I have calculated data (the column and row headings are calculated as well) similar to this for which I need to create an automatically updating chart.

******** language="JavaScript" ************************************************************************>Microsoft Excel - test.xlsm___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=
ABCDEF1 BalloonsFlagsCushionsPillowsStickers2Blue136013Red352104Green703245Yellow075056Orange558327White26073Sheet2
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The chart should update if the data is filtered; so if a user doesn't want to see the 'orange' row they can filter the data accordingly. There is a finite number of rows and columns in the range but sometimes there will be less data. I have tried to convert the data to a table and that works quite well except the legend keys persist: so if there is no data for pillows and stickers the legends still appear and have to be removed manually. Also, there seems to be a bug in Excel 2007 whereby the removal of a chart linked to table data causes a persistent error message to appear.
I know that I need to created a named range for the data series using OFFSET, and I have also read that I need to refresh this formula using VBA but I am now very confused and none of my attempts quite work.

View 9 Replies View Related

Chart Series - Set Start / End Dynamically

Apr 16, 2014

The formula used to create the marked series line.

Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:$A$122;Rolling_window_data!$B$3:$B$122;1)

The statistics in Rolling_window_data.

I need the marked line to be different from/to a point in time (x scale), depending on the backfill date in Rolling_window_data. All the contents are generated programmatically so the backfill date cell with a value will almost always be different. From what I've researched, I should make 2 series that overlap each other (in the current case, the 1st series should end on line 27 and the second should start from line 27 and continue to the end). The start is always line 3 and the end is always line 122.

How do I dynamically set the series start/end so I can make them overlap each other? I'm assuming I should add some kind of 'IF Statement' to the series code but I'm very new to excel formulas.

Code:
=SERIES(Rolling_window_data!$B$2;Rolling_window_data!$A$3:
IF(FOR Each Cell in Range("D3:D122") cell.value #N/A, Cell, "D122");Rolling_window_data!$B$3:$B$122;1)

View 2 Replies View Related

Dynamically Expand Range To Create Chart

Jan 21, 2009

See the attached example. I have data listed as shown, but need the chart to automatically update whenever a new sales figure is added to the next month. I know you have to use the OFFSET to create dynamic ranges but I can't get it to work horizontally, only if my data is laid out vertically.

View 2 Replies View Related

Dynamically Update Chart Source Range

Aug 31, 2007

I'm trying to create a chart and set its source dynamically.

I've tried two methods, the first by setting the XValues of a chart's series collection to a range specified by cells,

ActiveChart.SeriesCollection(1).XValues = range(Cells(9, coloffset + 4), _
Cells(9 + numvsteps, coloffset + 4))

And by using the chart wizard.

Call vchart.Chart.ChartWizard( _
Sheets(resultssheet).range(Cells(9, coloffset + 6), Cells(9 + numvsteps, coloffset + 6)), _
xlLine, , xlColumns, _
Sheets(resultssheet).range(Cells(9, coloffset + 1), Cells(9 + numvsteps, coloffset + 1)), , _
False, "End-End Eff., " & current & "A", "Line Voltage", "Eff. [%]", "test")

Setting the XValues property gives an "Unable to set the XValues property of a series class".

Using the chart wizard, trying to set the category labels parameter of the chart wizard to a range doesn't give an error message, but does leave the category labels section of the chart (when I click and view it) blank.

I've also tried

resultssheet = "Sheet1"
tempstring = "='" & resultssheet & "'!" & "R9C" & (coloffset + 1) & ":R" & (9 + numvsteps) & "C" & (coloffset + 1)
ActiveChart.SeriesCollection(1).XValues = tempstring

When I step through the code, tempstring is "='LineReg Results'!R9C1:R19C1", which is correct, but I still get an "unable to set the xvalues property of a series class" error.

Interestingly, if I record a macro of me setting the category labels, and play it back, I also get the error.

View 7 Replies View Related

Dynamically Change Links?

May 21, 2014

I have a link

='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13

Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes

im looking at

2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13

where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?

View 3 Replies View Related

Dynamically Change USERFORM

Jul 29, 2009

or it's text box/ labels depending upon learning of how many records the application is working with?

I remember some body here has said that you could either
1 - create many labels and text box and then once run the application hides the number of the ones not use or

2 - you could once run the application have visual basic create label and text of the right number associate with the number of records working with.

I have looked at both these option and have found nothing. So is this possible and show some examples if you have them.

View 9 Replies View Related

Macro To Dynamically Change Y Axis

Jan 2, 2012

I have used this code a number of times to automatically change the scale of x axes without problem. I have just used it however and it has condensed the data so that it the line only appears in the last quarter of the chart.

Code:
Option Explicit

Sub ChangeStaticAxisScales()
With Sheets("Static Charts").ChartObjects("Chart 2").Chart

' Value (Y) Axis
With .Axes(xlValue)
.MaximumScale = Worksheets("Raw Data").Range("AN7").Value
.MinimumScale = Worksheets("Raw Data").Range("AN6").Value
.MajorUnit = Worksheets("Raw Data").Range("AN8").Value
End With

End With
End Sub

View 3 Replies View Related

Dynamically Change The End Value Of A Range In A Function

Jul 20, 2006

I am using the STDEV function on a range of values: STDEV($I$26:I2545). My starting cell is constant and I would like to make my ending cell variable based on a value I enter in a master cell (A1). The value I enter in the master cell is multiplied by 252 the product would equal the row number I would like to stop at. So if I enter 1 the formula will be STDEV($I$26:I252). I tried: STDEV($I$26"I"&(A1*252))) and other such variations to no avail

View 5 Replies View Related

Change Size Of Range Dynamically

Aug 10, 2007

Need assistance with this code provided by SHG. See attached sample. I need to create a range called DaysOff so that this function will work. As it is the function is perfect; though the accuracy if the result is dependent on the exact range, thus I need this named range to be dynamic. I have tried but it does not seem to work correctly. The named range should refer to the list of names between 1-10. The range should dynamically include any names that are listed.

Public DaysOff As Range
Function datNext(datLast As Date, DaysOff As Range) As Date
Dim datBeg As Date, datEnd As Date
Dim iLastPd As Integer
Dim iDay As Long, nDay As Long
Dim iYr As Integer, iMo As Integer
iYr = Year(datLast)
iMo = Month(datLast)
datBeg = DateSerial(iYr, iMo + 1, 1)
datEnd = DateSerial(iYr, iMo + 2, 0)
iLastPd = Pd(datLast)
nDay = DaysOff.Rows.Count
Do.......................................

View 2 Replies View Related

Add Dynamically Vertical Line To Chart Without User Intervention After Initial Setup Of Workbook?

Mar 21, 2014

I am trying to add a vertical line dynamically to a chart. What I mean is I have a chart that will have a line type chart on it and I want to add a vertical line to it based on the output of one cell in that same workbook. I am making this workbook for use by end-users so I can't ask the end-users to make a bunch of modifications to the sheet or or chart. What I do has to just work when they use the workbook.

The line chart has an X axis of time and a Y axis of items sorted. (This is the part of the chart I have done already.) I want to add a vertical line fed from a cell which will have a time fed from a cell on the workbook. For example the line chart goes from 9:00 to 16:00 and the vertical line might be at 13:00. The cell feeding the vertical line is subject to change based on the other inputs on the workbook so that's why I say the vertical line has to be dynamic as the vertical line could be anywhere between 9:00 to 16:00 or it might even be that I can't place the vertical line because the feeding cell is outside the range of 9:00 to 16:00.

I have seen a number of ways to do this but none that are dynamic and automatic and don't require end user to adjust the workbook to make the vertical line at the correct on the time scale.

View 2 Replies View Related

Change Caption Of Dynamically Created Label

Jun 26, 2007

For a form that dynamically creates a series of text boxes and labels, does anyone know how to change the caption of a dynamically created label to display the value of a cell in a worksheet? The cell in the worksheet changes according to input on the text boxes. In the form's code, I have:

Dim newLblFreq As MSForms.Label
Set newLblFreq = Me.Controls.Add("Forms.Label.1", "lblfreq" & i, True)
newLblFreq.Caption = Range("V" & i).Text & " Hz"

But once the label is created, the caption won't change, even if Range("V" & i) changes...............

View 6 Replies View Related

Change External Link Dynamically With Cell

Apr 14, 2008

My Example

I have Master Book1 which has external links to Book2, Book3 and Book4.

My Formula in Cell B2 is ='D: empxl[Book2.xls]Sheet1'!$A$1

In Cells A1, A2 and A3 I have the text entries 2, 3 and 4 respectively.

Is it possible to use these Text entries to update the Formula I plan to copy down from B2 onwards?

i.e. Formula would read something like ='D: empxl[ Book"A1".xls]Sheet1'!$A$1

View 4 Replies View Related

Change Size Of Excel Data Table Dynamically

Jul 23, 2010

Is there a way to increase the size of the data table dynamically?

View 10 Replies View Related

Vlookup (name Range Change Dynamically When The Pivot Table Is Refreshed)

Oct 26, 2009

I have the following vlookup formula in my spreadsheet.

IF(ISNA(VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE)),0,VLOOKUP($A2,'Purchase Order Pivot Table'!$5:$500,67,FALSE))

It works perfectly for my current requirement, but it looks at data in a pivot table which is created from a dataset. If I name the columns in the pivot table so instead of looking at column 67 it looks at a name range will this name range change dynamically when the pivot table is refreshed? If not how can I get it to.

View 2 Replies View Related

Change Chart Series Pasted From Another Chart

Jun 19, 2008

I want to use a macro to change attributes of a series in a chart. Unbelievably, if the series has been pasted into the chart from another chart, and although the macro can address the series and even return values (eg name) correctly, the selection simply cycles to one of the original series on the chart.

View 3 Replies View Related

Single Data Series With Two Y-axes

Jun 23, 2014

So I have this given: Screen Shot 2014-06-23 at 3.53.01 PM.jpg and I am trying to fit my data to the graph I have in excel below. There is only one x-axis; suction pressure. However, there y values are dependent on two variables, discharge pressure and outdoor air temp.

How can I make the data on this sheet: Screen Shot 2014-06-23 at 3.53.05 PM.png

dependent on both axes?

View 2 Replies View Related

Changing Xvalue Axes To Minimum Series Value

Jul 11, 2014

I'm trying to change 115 charts so that the Xvalue axes (Y-axes) will start on the lowest value of my data series. This is what I've come up with so far:

[Code] .....

What is the syntax to set instead of X so that all Xvalue axes is set to the lowest Data series value in individual chart?

View 6 Replies View Related

Graphing Multiple Y-axes Datasets ($, %) On Same X-axis (years)

Mar 6, 2009

I have the theoretical set:

year $ %
2003 100 10
2004 120 20
2005 145 30
2006 205 40
2007 300 50

Could I make the year the X-axis and make 2 y-axes on the same graph in excel that each datasets would be correlated to (eg. for money it'd be from 0 to 300 while for percent it'd be from 0 to 50)

View 3 Replies View Related

Graphing Price Elasticty Of Demand & Supply On Same Axes

Apr 18, 2006

For an economics assignment I would like to graph price elasticity with price on the 'y' axis and units on 'x'. I have not found a way to do this in Excel, can someone assist?

View 6 Replies View Related

Chart Macro: Update Using A For-loop

Feb 11, 2009

I'm trying to make a scatterplot so what I did was recorded a macro and now I want to be able to update it using a for-loop. Here's my code.

View 4 Replies View Related

Loop Through Worksheets And Format Chart 1 - Runtime Error

Jun 27, 2014

I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:

Code:
.ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"

Here is my for each loop:

Code:
Sub UpdateTextAllStateCharts()
'This macro loops through worksheets in a list and performs the assigned task
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("C:C")
If sheet_name.Value = "" Then

[Code] .....

This line is also causing the same error:

Code:
With .Characters(42, 68).Font

View 2 Replies View Related

Loop Through Data And Create Chart With A Number Of Series

Apr 8, 2008

I am looking to create a number of series on a chart (xyscattersmooth) from a datatable. Below is my data Table....

View 9 Replies View Related

Loop Able To Change The Last Caracter (pg=2) In VB

Dec 8, 2009

I'd like to extract multiple page from a web site. I'd like to use a for loop but I'm not sure about how doing it. The problem is that the variable to increment is in the url. I want to generate a for loop able to change the last caracter (pg=2). I tried to simply replace the number by the variable but this generate error in the code.

View 4 Replies View Related

Change To Another Variable In Loop?

Jul 30, 2014

I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).

Code:
For Test = 1 To aNo
With ("A" & aNo)
.Select

[Code]......

View 9 Replies View Related







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