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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Jul 23, 2010
Is there a way to increase the size of the data table dynamically?
View 10 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
