Charts.Add Adds Pivotchart, Error On SetSourceData

Jan 23, 2007

I have the following extract of code which is meant to add a chart. However, it adds a pivotchart and then errors on the last line because it can't change the source data on a pivotchart like it can on a non-pivotchart. All of the other charts in the workbook are pivotcharts. It was working fine last week.

Sheets "LOI Sheet" and "Data for Charts" exist.
"e" is a defined integer with value 53 at last running.

Charts.Add After:=Worksheets("LOI Sheet")
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Data for Charts").Range("D2:D" & e), PlotBy:=xlColumns

View 9 Replies


ADVERTISEMENT

Formula That Adds Both VL And DVL In A Column What I Have Now Only Adds The One

Jan 6, 2010

=COUNTIF(F3:F38,"=VL") This works for totaling the VL in the cells in the column but what if I need it to add DVL in the same column to that total? So I need it to add both VL AND DVL as one total. I can make it add one or the other but not both

VL DVL
VL VL
VL
VL
VL

5 2

View 2 Replies View Related

SetSourceData Chart

Sep 22, 2009

SetSourceData Chart. I'm using the following code

View 2 Replies View Related

Update PivotChart On Another Sheet Via Code

Sep 7, 2006

I have created a pivotchart and placed it on (say) sheet " GRAPH". Excel has created the associated pivottable and stored it on (say) sheet "PIVOT".

Because the formating of the graph is lost each time the pivotgraph is refreshed i.e page fields changed, i have written a small macro to reformat it which i have put in the "Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)" event in the "PIVOT" sheet. All appeared to work well.

However, on closer investigation, i noticed that the pivotchart wasn't being updated (even though the source pivottable was set to refresh on open - another problem for another day?) so on the pivotchart sheet i had to press the little red exclamation mark to update it and the chart changed before my eyes. I thought I would then record a macro to see what i had to do.

The code the macro recorder gave was "ActiveChart.PivotLayout.PivotTable.RefreshTable" so i simply inserted this into the chart.activate event of the "GRAPH" sheet and assumed it would kick in everytime the graph was activated.

However, it crashes everytime with an error message "unable to get the pivotfields propety of the pivottable class". If I put an msgbox in the relevant "Sub Worksheet_PivotTableUpdate" to display the target. name, its blank!

I've tried all sorts of work arounds like trying to update the pivottable in the graph activate event:-
dim pt as pivottable : set pt = ......... : pt.refreshtable etc
select the sheet before hand etc, etc but no joy.

Following the code through debug it always crashes with the same message. If I manually update the pivottable on the PIVOT sheet (right click "refresh data") the table gets refreshed which then triggers my graph reformat code and everything is fine.

I'm sure i'm doing something wrong but it doesn't appear to like the chart and table being on different sheets?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

msgbox Target.name <---------ERROR (Blank!)

If Target.PivotFields("BusinessArea").CurrentPage = "XXXXXX" And _
(Target.PivotFields("BM").CurrentPage = "AAA" Or _
Target.PivotFields("BM").CurrentPage = "BBB" Or _
Target.PivotFields("BM").CurrentPage = "CCC" Or _
Target.PivotFields("BM").CurrentPage = "DDD") Then
MsgBox "Error - " & Target.PivotFields("BM").CurrentPage & " is not a valid Business Manager of " & UCase(Target.PivotFields("BusinessArea").CurrentPage) & vbCr & vbCr & "Please try again!!", vbCritical, "Error"
Exit Sub
End If

..graph reformat code (irelevant because even If i delete this it still bombs out!"
End Sub

View 5 Replies View Related

Change Source Data Of PivotChart

Aug 30, 2007

When attempting to use the following

charts.add

activechart.setsourcedata thisworkbook.worksheets("PTPage1"). range("A1")

I receive the following error: Run-time Error '1004':

The source data of a pivotchart report cannot be changed. You can change the view of data in a pivotchart report by reorganizing its fields or items, or by changing its associated PivotTable report.

View 2 Replies View Related

Custom Error Bars On Bar Charts

Apr 24, 2009

I created a bar chart with horizontal (category) labels: winter, spring, summer, fall. Y-axis label is percent. For each category there is data for blk rats, Norway rats, Mice. For each species and category the standard error is different because it is based on several years of data for a given season.

When I try to display the std. error (using custom, fixed value) it treats each species as a series and only lets me choose one value. in other words the std. error would be the same for blk rats in winter, spring, etc. How can I use the individual values?

View 4 Replies View Related

Exporting Charts As GIF - Error '1004'

Oct 14, 2004

I have an Excel application which attempts to convert charts to gif images,
using the code (ChartPath is equal to a valid directory location):

ActiveChart.Export Filename:=ChartPath, FilterName:="GIF"

This works perfectly on one PC, but fails when I try to run it on a
different PC. Both PC's use Windows 2000, and both run Excel 2000.

The error message I get on the second PC is:

Run-time error '1004':
Method 'Export' of object ' _Chart' failed

What I need to find out is what could possibly be causing this statement to
fail. Is there something in the Excel or Windows settings that could be
different (a dll file etc)?

View 9 Replies View Related

Dynamic Charts & Error Message

Sep 14, 2006

I have created a dynamic chart, courtesy of the Jon Peltier website. I have 2 problems. No problem with getting the chart to work, the problem comes when I delete the data. I get an error message telling me that 'A formula in the worksheet contains one or more invalid references.' The other problem is that I cannot copy the worksheet to another worksheet in the same workbook. Well that is not strictly true, I can copy it but it wont updat. Any ideas as to how I can overcome this? Any suggestions welcome, apart from 'Don't delete the data then'

View 9 Replies View Related

Dynamic Ranges For Error Bars In Charts

Jul 5, 2006

I am using defined names to build a chart. This is working fine, but I also would like to use a defined name to control the +/- fields for the error bars. The defined names I have set up are correct, and I am able to enter them into the sorce data form but Excel does not display them.

View 9 Replies View Related

Macro Runtime Error - Items (charts) Not Found

Oct 3, 2013

I've got an interactive chart on a sheet where users select a chart from a combo box control and a picture link to the chart is displayed. The actual charts are on a hidden sheet. I have to activate the charts first for it to work so I put a button the sheet and recorded a macro where I unhide the hidden sheet, select each chart as I scroll down the screen by clicking on it then hide the sheet again and return to the interactive chart.

My problem is that I'm getting an error when the macro tries to select the first chart which is called "Chart 6":

Run-time error '-2147024809 (80070057)':

The item with the specified name wasn't found

I recorded the macro so I'm not sure why the macro isn't working now.

Code:
Sub ResetCharts()
'
' ResetCharts Macro
' Reset all charts
'
'
Sheets("Budget v Actual Graphs").Select
Sheets("Graph BG").Visible = True
ActiveSheet.ChartObjects("Chart 6").Activate
ActiveChart.ChartArea.Select
ActiveWindow.SmallScroll Down:=15
ActiveSheet.ChartObjects("Chart 35").Activate

[Code] ............

View 5 Replies View Related

File With Multiple Charts Always Gives ONE Invalid Reference Error Until Pressing F9

May 22, 2012

I have a file with data in one sheet (unfortunately I cannot share the file because of confidential data..), and two sheets with both 9-11 charts in them.

The charts are all filled simply with a dynamic range from the datasheet, so that Last Year and all months from this year with data in them are always in there.

The problem: When opening either of the graph sheets, the (familiar?) error pops up: "A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name and cell reference."

One of the graphs has an error and only shows one point of data in it. But only until I press F9, and then the chart is repaired and functions perfectly like all the others!

I have tried lots of things, including checking all source ranges, deleting all graph names etc. and then repaired it so it would work again. The only thing that happened, is that the same error now pops up for another graph in the sheet. Still, when you press F9, the graph functions again!

View 6 Replies View Related

Charts Stop Plotting Data Error Bars After Certain Number Of Data Point Selected

Dec 23, 2013

In the attached document is a timeline made from a scatter chart. Error bars using custom values are used to show the length of each task, however I can't get the chart to include error bars for the last 2 data points (tasks).

View 1 Replies View Related

Resource Error Message "cannot Complete This Task With Available Resources" When Changing Charts Thru VBA

Apr 19, 2006

I have the following code for a sheet in my workbook that has 3 charts:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.Calculation = xlCalculationManual
ActiveSheet.ChartObjects("RdteObs").Chart.SetSourceData ThisWorkbook.Names("GSumRdteObs").RefersToRange
ActiveSheet.ChartObjects("RdteWip").Chart.SetSourceData ThisWorkbook.Names("GSumRdteWip").RefersToRange
ActiveSheet.ChartObjects("RdteExp").Chart.SetSourceData ThisWorkbook.Names("GSumRdteExp").RefersToRange
Application.Calculation = xlCalculationAutomatic
End Sub

but whenever the sub runs, I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." Does anyone have an idea what's going on?

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

Pivot Charts Versus Charts

Sep 5, 2012

I am trying to put some charts into a report that is pivot table based. I have some code that will work if the pivot tables stay stagnic but the users may change the tables around so that could be an issue of new data. I have the following code where I changed the source to the pivot table name (general name because of numberous report possibilites). I have taken out the other charts because they are just a variation of the chart 1. I am crashing on the line with the * on it.

Sub UWTierChart()
Dim oCell As Range
Dim oChart As Chart
Set ws_data = ActiveSheet
'Chart 1
Sheets(wsPT).Select
Range("B21").Select

[code]....

View 2 Replies View Related

IF Statement That Also ADDs?

May 13, 2013

I'm providing a tool for users that:

- allows the user to select Y / N for multiple criteria

- based on the criteria, calculations are made

- based on the criteria and the calculations, totals are calculated

Here's the formula that's currently NOT working for me . . .

=IF($M8="y",$M12*V$9,IF($M8="N",0)+IF($N8="y",$N12*V$9,IF($N8="N",0))+IF($O8="y",$O12*V$9,IF($O8="N",0)
+IF($P8="y",$P12*V$9,IF($P8="N",0)+IF($Q8="y",$Q12*V$9,IF($Q8="N",0)+IF($R8="y",$R12*V$9,IF($R8="N",0)
+IF($S8="y",$S12*V$9,IF($S8="N",0)+IF($T8="y",$T12*V$9,IF($T8="N",0))))))))

Currently, returns a value of '14' -- shown in the visual provided, should be a value of '21.'

View 8 Replies View Related

Automatically Adds A Row Down

Aug 20, 2009

all the data from the form is successfully placed into the assigned work sheet.

My question is

What is the code i have to Assign so it automatically adds a row down?

right now it adds the info but every time I try and put something new it overrides what was placed before..

View 9 Replies View Related

Adds Up Various Cells Then Add The Same Cell

Jan 17, 2007

I have a spreadsheet for my local camera club that needs a few "IF" formulas that I have not a clue at how to accomplish. I have done the rest of the spreadsheet.

The formula needs to be

IF cell A1 = 22 or 23 or 24 (a manually entered number) then the answer = 1
IF cell A1 = 25 or 26 or 27 (a manually entered number) then the answer = 2

Also need another IF formula that adds up various cells: A1 + A2 + A3 + A4 and then adds the same cells on a 2nd and 3rd worksheet, and if the total is zero, then the cell will remain blank in the answer field, rather than having a zero in it.

View 9 Replies View Related

A Sum Formuala That Adds Up All Sums

Sep 4, 2008

Is there a formula or a function that adds up all of the sums in a row.

******** ******************** ************************************************************************>Microsoft Excel - Book4___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA14=ABCD14603 Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Macro Adds A Bar On The Screen

Oct 10, 2008

I have a macro launched from a command button that sometimes puts a bar at the left side of the window. When it executes the line Sheets("graph").Select the first line in the macro it puts the bar at the right side of the window. The characteristics of the bar are:

1) It is associated with the main excel window - if you minimize the window containing the worsheet the bar is still visible

2) It is one of two widths, about 1/4" or about 2.5"

3) If you move an Excel toolbar to the left side of the screen the bar disappears. If you move the toolbar back to the top of the page it doesn't return but it can appear the next time you run the macro.

4) It only appears some times you run the macro - I can go days without seeing this problem and then it happens every time I run the macro.

View 9 Replies View Related

VBA Which Adds Text To Subject Of Email

Jul 22, 2014

I currently have the below in my VBA which adds the text to the subject of an email:

Application.Text(today - 1, "MM-DD-YY")

today = Int(Now()) is part of the macro. How do i format this to display workdays only.

For instance if today is 07/21 (monday) i need this to display 07/18 vs 07/20.

In excel that function would be =WORKDAY(A1,-1) where A1 is =Today(), just not sure how to translate this to VBA.

View 5 Replies View Related

Adds Up And Show A Total At The Bottom

Feb 13, 2008

i have a list of parts that adds up and shows a total at the bottom (great, works perfectly)

in the next column i have a percentage (margin) which again works perfect

ie.

Col A Col B Col C Col D Col E

1 ENGINE 4500 5000 11%
2 SURCHARGE 1800 2000 22%
3 OIL 1 2 100%

Totals 6301 7202 ??%

what i would like to do (forgive me if i have missed something) is for the total value in colum D to add up but for the % in column E not to be included in the total %

Is it possible to have a situation that when the word"SURCHARGE" is in the row then the % will not be added to the total %

In other words i need the surcharge price to be included into the final price, but the surcharge would give me a false figure so that needs not to be included in the total %

View 9 Replies View Related

Vlookup Returns Correct Values But Adds Them As 0

Apr 7, 2014

People usually keep track of their own points, however an official would need to confirm that they are correct before approving a purchase. However 99% of the time, people have the incorrect point totals.

I am currently working on a book to automate the system. It means players wouldn't have to keep track of their own points and that the point totals are always up to date. The first sheet in this workbook is a summary page which has player names, points from posting, bonus points, level up costs, purchase costs etc. Then there is a database with all the costs, which I pulled from the site. Then there are numerous record sheets, which have the player name, the item bought/levelup purchased, and the cost, which is pulled from the database via vlookup. Here is where it gets irritating.

On the summary page I attempted to use SUMIF in the total fields (total cost of purchases, total cost of leel ups etc). It would basically search the record for any entries with the name matching that of the player, and add the amounts associated with that entry. However it would always return 0. After doing some playing around I came to the conclusion that whilst the VLookup returns the correct values, functions like sum see them as 0. So instead of it going 5+7+7+10=29, it says 0+0+0+0=0.

View 13 Replies View Related

Adds A Consecutive Digit To A Repeating Cell Value?

Mar 12, 2014

I have the following code which adds a consecutive digit to a repeating cell value in:

=a2&countif($A$2:A2,A2)

So, this formula has put in cell B2. This means, that if if A2:A5=1 for each row, then B2=11, B3=12, B4=13, etc.

What I'm trying to do is put "-" between the original value and the extra digit. So, B2 would instead look like 1-1, B3=1-2, B4=1-3, etc.

View 2 Replies View Related

TEXT Adds 15 Decimal Places In The Cell?

May 10, 2014

AVERAGEIFS(X$2:X$1000,$AT$2:$AT$1000,1)& TEXT(BE2/BD2," 0%")

The formula is working , but the result is : 3.50239234449761 58% Formatting the cell did not work. Can these decimals be slashed?

View 5 Replies View Related

Excel Adds Extra Line On Some Files

Jun 20, 2014

I have a really strange issue on one machine. The end-user gets Excel reports emailed to him. When he opens certain ones, there is an extra space (almost a line without borders) on certain rows. This only happens on some of the files he receives

AND

This behavior is only on his system. On all other recipients' machines the spreadsheets do not have the extra space on some rows. It does not matter if he opens the attachment within Outlook or saves and opens in Excel from the save location.

I have tried resetting the defaults and that does not work.

View 2 Replies View Related

Create A Function That Adds A Predetermined Amount?

Aug 4, 2014

I need to create a function that adds a predetermined amount (lets say 120) to a cell (lets say A1) every time another cell (lets say B1) reaches a 10 (like 10,20,30,40 etc)

Lets say the B1 is 30, i need A1 to recognize that it needs to show 360 as its value (adding 120 for every 10 of B1)

View 2 Replies View Related

Calculation/formula: ADDS Cells B1 And A1 (B1+A1) IF B1 Is Negative

Dec 15, 2008

How can i do this using Excel 2007. I have to cells, A1 and B1. A1 is always Positive whereas B1 might be Positive or Negative. I need a calculation/formula in C1 that ADDS cells B1 and A1 (B1+A1) IF B1 is Negative and SUBTRACTS A1 from B1 (A1-B1) IF B1 was Positive.

View 3 Replies View Related

Basis SUM Macro: Adds All The Numbers In A Column

Nov 15, 2008

I would like to have a macro which adds all the numbers in a column. The problem is that sometimes there are less sometimes there are more numbers. What I would like is, that the macro to sum the numbers right under the last cell which contains a number.

View 2 Replies View Related

Function: Constantly Adds The Numbers In A Column

Jan 25, 2009

I don't think there is such a function but is there a way that I can have a function that constantly adds the numbers in a column and when the sum of 240 is reached it adds 30. I need it to do this only once and not keep adding at every 240 point.

View 2 Replies View Related







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