I am making a macro to update the data on a bunch of charts. This is what I got from the macro recorder when I selected the data manually.
Code:
Sub testthree()
ActiveWorkbook.Charts("Julian's Words").Select
ActiveChart.SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
"C1:H1,C5:H5"), PlotBy:=xlRows
End Sub
I have created the part of the macro that will go to the data table and define the two ranges I need to use.
Code:
Sub Adjust_Graphs()
Dim lastdate As String
Dim daterange As Range
[Code] .....
However, this code is selecting the entire range C1:H5. How can I get the code to take JUST the two lines I need?
Exmaple: The X axis needs to be C1:H1, and the Y axis needs to be C5:H5.
I also tried this, without success:
Code:
Dim combrange As Range
Set combrange = Union(daterange, wordrange)
ActiveWorkbook.Charts("Julian's Words").SetSourceData Source:=Sheets("Super Sight Chart Data").Range( _
combrange), PlotBy:=xlRows
I've been updating all the charts manually each time I update the entries in the worksheet which charts are created from in the same workbook. Is there any way (eg. by use of macros or VBA) that I can have all the charts updated automatically when data ranges are updated each time?
I have attached a copy of the chart and data. My chart starts on the bottom. I had to take out a lot of data but beneath the data shown is much more data..
I want my chart to automatically update when new data is added. Here are the issues. I have several lines of headers and below that my data. Then below my data I have other data that I don't want displayed in the chart (its not shown here because of size limitation of attachmnet).
The new row will be added each time right below the header, right above the most recent data given. So a row will be added above row 8!
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
I have some code to plot a column chart of data but it isn't working as expecting at the moment. The code is below. The variable binCounter is a count of how many cells in a range that I want to plot on the chart.
However, what I am finding is that the first couple of cells in the range appear as the series name with the rest appearing as the data in the chart. Secondly, the chart appears with the axis labels 1,2,3 etc when I have some custom ones I would prefer to use. How do I go about setting this property, as I can only find options on setting the axis title There is a lot of stuff on XY charts on Google but I can't find much on column charts unfortunately .
VB: 'activate sheet and chart Worksheets("Home Page").Activate ActiveSheet.ChartObjects("Histogram").Activate
I have created a line chart that plots weight of a person, but want to show on the plot area what a certain range of weight represent, Ive attached images to show this concept.
I have a chart in a workbook that works fine by looking at the following data series:
=Workings!$A$2:$C$35
In the data series above...where the numbers are 2 and 35 I have named ranges "CHART_Start_Row" and "CHART_End_Row" which will change dynamically as appropriate and can replace these static numbers - but how do I incorporate these named ranges into the data series reference?
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem ... .Fields.Item(1).value = activecell 'activecell value = "Joseph" If Not isempty(activecell.offset(0,1)) Then .Fields.Item(2).value = activecell.offset(0,1).value Else .Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works End If ... End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty ' or .Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
I want to update a number of charts using data that covers a rolling 12 months - this will be amended each month by addition of new data but I will be retaining the full history. I thought the easiest and quickest way would be to name the data range and just amend it each month as I added new data. However, when I use the named range in the chart wizard it is replaced by direct cell references. Can anyone suggest how I can retain the name in the chart or suggest an easy alternative method?
I need my SQL server query to update with names from a range. e.g. pinnog as shown below is a name defined range called Name1 and smithb will be Name 2.
Can someone with me with this code as I am not sur ehow to lookup ranges in VBA.
I have a column. D3-D7 have numbers in them and I want to add them. I put a formula in D8 to SUM all the Cells From D3-D7. No problems there.
When I insert a row above D8 the range for the SUM is not expanding, and if I do put info in the cell a box comes up asking if I want to update the formula.
Is there a way to insert a new row and have it be included in a range?
in A1 there will be a box with LinkedCell set to A1 and ListFillRange set to B1:B5. I now want a similar box in A2, with A2 as LinkedCell. However, when I copy paste the box in A1, it still refers to A1 and B1:B5 (the last part is ok). I can easily change A1 to A2 by just changing it in the properties, but the thing is that I also need these boxes in A3:A100. Someone else helped me out by writing some code, but this is not working correctly yet, probably because I'm doing something wrong.
Also, the ListFillRange might expand in the future. Therefor I would like to know if its possible to select the boxes in A1:A100 and change the ListFillRange to B1:B6 for all at once, or even have a piece of code attached to a command button that recognizes the expanded range and updates it.
I couldn't attach the file because it was to big, therefor I uploaded it with yousendit: [url]
So in short: I would like all the comboboxes in column E to be linked to the cell underneath and have and option to adjust the listfillrange. The same for column F, but for this one the listfillrange needs to be O8:O11.
I am using a VBA macro which performs a Yahoo webquery to obtain stock data. After the data are downloaded, a chart is updated with the new data.
The current macro updates the scaling on the price axis just fine, however, if I change the time frame the x axis, i.e, time/date axis is either spread out to far or jammed up.
The code for re-scaling the the y axis is shown below:
Code: Sub UpdateScale() Dim ChartVar As Chart Dim lMax As Long, lMin As Long On Error GoTo ScalingProblem 'Assigns the values in the Min and Max ranges to variables.
[Code]....
Any similar sub that would also rescale the x axis as needed when the data intervals are changed?
I'm trying to change the source data for a worksheet for a series of charts. The source data appears in a series of tables. There are about 30 such tables and associated charts on the worksheet. (I need to duplicate this many times over, which is why I need to automate.). The chart names seem to increment by 2 (i.e. Chart 1, Chart 3, Chart 5) And the source data for the charts increment by 9: BM95:BP102, BM104:BP111, etc. How can I automate this? Also, I'm trying to automate changing the reference in the title of the chart. This reference increments rows by 9. When I use the macro recorder to do this, I get the following
I have a macro that extracts data from a source workbook. The data is owerwritten every time that I use the macro. Now I have a graph that everytime I use the macro does not update with the new data, Is there a clever way to do that? The code for extracting from the workbook is the following:
Sub Extract() Dim myFileName As Variant Dim SourceWkbk As Workbook Dim CurrentWkbk As Workbook Dim testWks As Worksheet ActiveWorkbook.Sheets("CURRENCIES").Select ActiveWindow.SelectedSheets.Delete myFileName = Application. GetOpenFilename("Excel files,*.xls") If myFileName = False Then Exit Sub 'user hit cancel End If................
I am trying to create a pie chart that does not include the data labels if the value is zero. I have researched on the 'web' and have been told if I get a value of #NA then these would not show as they are not considered as a value.
However using the following formula I am getting a result of #NA in the cell but this is still showing as zero in the pie chart and hence the labels.
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.
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.
I have a spreadsheet with 300+ questions in columns (covering 2 worksheets - rows 1 to 5), and the answers to the questions are placed in the rows beneath - column A & B lists who the answers came from.
The idea here is that on a seperate sheet a user can enter a question number (e.g: 1.2OP007) and a graph will update to show the different values given for each person who answered that question.
So, in this seperate sheet I have the question in cell $A$1. In cell $B$1 I have this formula:.........................
I have a stock chart that I want to update dynamically if the data record length changes. From the source data menu, I tried to use the following data ranges:
=OFFSET(Candles!$AB$8,1,0,COUNTA($AB:$AB)) ---> Date, x values =OFFSET(Candles!$AD$8,1,0,COUNTA($AD:$AD)) ---> High =OFFSET(Candles!$AE$8,1,0,COUNTA($AE:$AE)) ---> Low =OFFSET(Candles!$AF$8,1,0,COUNTA($AF:$AF)) ---> Close
When I place any of these in the data range box under 'x axis labels or values', I receive the message "This function is not valid".
The MS webpage at http://office.microsoft.com/en-us/ex...098011033.aspx describes using these formulas but they don't appear to work.
I want have a chart that use Formula result to update automatically. I know there is some vb methods to do this but my job is different and that ways work when values enter manually but i need a way that use formula result to update and don't make chart as long as existing values. I attached a sample of i need and i did explanation on it. The Salesman's are enter to column A by a VB and Their values of cost or earning are enter by a formula.
I am having difficulty figuring out how to get my chart title to automatically update when my chart changes via filtered criteria. I can get it to change by clicking the title bar on the chart, but would like the title to automatically update as soon as I select new criteria and the chart changes.
I created a chart in Excel 2003 which relies on dynamic named ranges. For years this chart has been working beautifully. I recently converted to Excel 2007 and the chart no longer understands the dynamic named range for the x-axis. It understands the y-axis dynamic named ranges just fine. I have tried renaming the x-axis range to various names (none of which contain the word chart). I also experimented by just using a static range name. And I have tried recreating the chart from scratch with the same results. It lets me create the chart initially but then when I refresh the worksheet, the x-axis is blanked out.
I am generating a chart through a macro. I am using 4 non adjacent range and the end point of the range is represented by a variable as the end point is not fixed. I am using following code.
I have built a chart using dynamic nameranges. The problem that I am facing is I tested it on two systems . One system the charts works fine but the other system the charts does not work . Both are excel 2007. Now the error that I get is if I see the chart data source from one system it is showing "!". The other file shows the chart data source as "0!". What should I be checking so that it works in the other system as well?
