Altering Data On Chart With Vba?
May 21, 2013
I have a very large workbook, too big to cut down and upload . In it I have some code which selects a graph and sets the data on the graph to an arbitary number, Unfortunately, I get a "Method 'Xvalues' of object 'Series' failed" error when I run it (although not every time). The relevant part of the code is below. Prior to the code below, different sheets are selected and variables moved around, then this part of the code hides the other sheets leaving the calculation page visible.
The graph is on a log-log scale, but I don't think that is particularly important. The data series are named as various things (e.g. baseline, reference, measurement) though but there are only ever two on the graph (I just rename them later on and change the data).
VB:
'open calculation page and hide survey results and archive pages
With Worksheets("Calculation Page")
.Visible = True: .Select
[Code]....
View 9 Replies
ADVERTISEMENT
Mar 31, 2014
I need to modify the following formula,
=IF(G5=-105,-105,-G5-IF(ABS(G5)>251,40,IF(AND(ABS(G5)>221,ABS(G5)<251),30,
IF(AND(ABS(G5)>180,ABS(G5)<221),20,IF(AND(ABS(G5)>=100,ABS(G5)
[Code].....
View 10 Replies
View Related
Mar 26, 2013
I have come across another stumbling block with VBA (new on VBA and slowly taking baby steps). I have a macro that reads and deletes all duplicates on the files i work on. However I need this to read and do the job on 2 sheets at the sametime (sheet 2 and sheet 3). Below is the macro which does work fine on a different workbook. How would I integrate this into my new sheet to do 2 jobs at once?
VB:
Sub delduprows()
Dim m As Integer, j As Integer
Dim n As Long, i As Long
[Code]....
View 1 Replies
View Related
Jun 12, 2014
I have a spinbutton on a userform. Here is the section of code for SpinUp:
[Code]....
Normally, I wouldn't hard code an address in like this, but I was given a last minute project to present on Monday, so it's down,dirty, and quick. Anyways, the adjacent cell "O7" has a formula referencing another page. When I spin the spinner it activates the correct cell (and continues to do so as long as I press the button), but here's the wierdness: cell "07" formula disappears, leaving the existing value in it. It's like it copy pastespecial values.
I've stepped through the code and it's not referencing any other procedure. It happens on this line:
[Code] ....
View 1 Replies
View Related
Sep 4, 2007
In cell AD17, I have a formula that returns the number of days or draws(Skips), since the number in cell AD2 has been drawn. The following cells AD18.......... returns the next skip and continues untill all the hits( AD3), are accounted for. My question, Can the formulas be altered to read a "
View 9 Replies
View Related
Jul 18, 2006
how does one alter a variable without referring to it directly? Eg. One has a combo box (CboPNum) with values from 1 to 20 and textboxes (TxtN1 to TxtN20). Upon selecting a value from the CboPNum dropdown, the background of the corresponding TextBox should be changed. I’ve tried to create something that joins the string “TxtN” and the CboPNum value:
ThisTextBox = “TxtN” & CboPNum.Value
ThisTextBox.Background = Black
But this quite correctly gives the error “Object required”… What do you guys recommend?
View 3 Replies
View Related
Apr 3, 2008
i have on sheet one a cell that requires an order number ( e.g something like j2345a ) what i would like to do on sheet 2 cell c10 i would like to extract the letter after the 4 figures so that what i would see in that cell is j2345.
what i should add is that the letter after the four numbers isnt always an "a" so it could be any letter that needs to be removed
i also need cell c10 on sheet 2 to remain empty if there is nothing entered in cell e8 of the first sheet
View 12 Replies
View Related
Dec 28, 2012
I have a column ful with numbers and would like to add an h (hour) after every number. I found that it is possible for valuta (dollar sign). Is there a way for random symbols that are not in the valuta list?
View 3 Replies
View Related
Nov 12, 2008
I have two tables of information. The first is a matrix with some distances. The second is a table of distribution. Ive been trying this for a long time now with limited success using a truth table but i've realised the only way to do this is in code. Ive got limited experience with this so please point me in the right direction.
Ive written some steps explaining what each table does here -
Step 1Check for lowest value B4:F4 in Table 1 (in example is 10)
Step 2Check corresponding column destination available capacity in table 2 (Example 500)
Step 3Distribute as much as possible from source in table 2 (500)
Step 4Reduce value in capacity line by value taken from source
Step 5If some source remains move back to table 1 and find next nearest column Dest
Step 6Repeat step 2 until all source is gone in row
Step 7 Check for lowest value B5:F5 in Table 1
if run out of capacity at all sites stop code
etc
until Table 1 column B is empty
Ive posted a spreadsheet with some before and after tables in it aswell. Its very small and formatted o its easy to see whats happening.
View 14 Replies
View Related
Apr 17, 2007
I have a workbook with four worksheets. Sheet1 is the master. On sheet one there is information in a 10x10 table. The first column of the table contains a status of "No, Maybe, Yes." A record will start with a status of No and move to Maybe and then yes. What I want to do is be able to dynamically fill sheet2-sheet4 with information from the master. If the status is "Yes" move that row to sheet2. If the status is "no" move that row to sheet 3. Etc. Further complication that is depending on status, I want specific columns from the master, not necessarily all of them.
View 2 Replies
View Related
Jul 6, 2007
I have a master spreadsheet containing 4 relevant fields, 'Manufacturer', 'Product', 'Version' and 'Type'. This contains all possible variations of 'manufacturer', 'product' and 'version' that can occur in the organisation.
The type field shows how the particular item is going to be processed, either 'automated', 'manual' or 'non'
This is a computer generated spreadsheet, apart from the 'Type' field which we've entered for each one individually, as are the user spreadsheets which dont yet have the 'type' field.
This is a large list of around 7000 items
I then have a multiple spreadsheets (one for each user, totaling around 1200) that has a list of a couple of hundred items that are relevant to the user which contains 'manufacturer', 'product' and 'version'.
i need to sort these out quickly by comparing the sheet to the master spreadsheet so where 'manufacturer', 'product' and 'version' match up with the masters record it automatically removes the the 'non' type of item from the user sheet and placing either 'automated' or 'manual' in a new 'type' filed where appropriate
View 3 Replies
View Related
Mar 31, 2009
--------------------------------------------------------------------------------
Ok i have a formula that i use to convert bond prices, which are in 32nds to numbers that excel can understand. I use the formula:
=LEFT(F7,FIND("-",F7)-1)+SUBSTITUTE(RIGHT(F7,LEN(F7)-FIND("-",F7)),"+","")/IF(RIGHT(RIGHT(F7,LEN(F7)-FIND("-",F7)),1)="+",64,32)
This allows me to type 101-16 in and excel recognizes this as 101.5
My problem is that my data I am copying into the sheet is often in 1/8ths. So the price will come in as 101-272, which means 101-27 and 2/8 (or 1/4).
I admittedly dont understand this formula and just use. Can someone help me tweak it to recognize the third digit?
View 11 Replies
View Related
Feb 8, 2012
At work, I have a workbook with multiple tabs that contain lists. Each tab has a corresponding Pivot Table.
There is a business requirement that a user can specify a name, which filters the data. For the sake of simplicity use this for an example
Column Headers: First_Name ; Last_Name; Age
Row 1: Johnny; Bravo; 29
Row 2: Shane; Falco; 34
Row 3; Bobby; Shane; 15
The user specifies "Shane" as the filter in another pre-determined Cell (D1). Using advanced criteria, I need to find all rows that have Shane in either First_Name or Last_Name. The only way I know how to do that is inserting two rows and adding criteria:
First_Name ; Last_Name; Age
=D1; ;
;=D1;
Johnny; Bravo; 29
Shane; Falco; 34
Bobby; Shane; 15
With Criteria Range = "A1:B3"
This is problematic because my Pivot tables now include 5 rows of data.
View 3 Replies
View Related
Nov 17, 2009
Convert the first letter of a string to a capital without altering the rest of the strings format.
example: the activecell contains the string a1st (lowercase a, superscript 1st)
how can i achieve this in VBA?
Additionally, is it also possible to convert all of a string to capitals except the superscript characters
View 9 Replies
View Related
Jan 14, 2014
I need to plot various data on top of stock charts.
This data could be irregular in date: it could be weekly, or simply random.
To plot this data by itself requires a Scatter Chart.
From what I know so far, you CANNOT DO THIS.
However, I suspect this could be done if I build a Stock Chart from scratch using a Scatter Chart.
Error bars can be used to make the tails, however, I don't know how they built the body bar which has the characteristics of a bar (border, and interior.)
But since I don't really need those two characteristics, I just need a wider error bar line that is provided in the chart edit window. I'm guess through a macro, there are wider line widths assignable.
View 5 Replies
View Related
Nov 27, 2011
I am looking for a creative way to display a pie chart within a data point marker of a line chart.
My database has 3 value columns, Type1, Type2 and the Total (Type1 + Type2)
these are recorded per day (Date, in Column A)
I have a line chart that displays the total by date, but I want to find a way to display the percentage split of a particular day by type.
I was thinking to load the chart image into the Data point marker, but i don't think that is the best way as the data is updated daily and I would have to do it each day for a few line charts.
The other way I was thinking about was to have a generic Pie chat in the Line chart (Maybe in a corner) and the pie would update depending on way date series was selected or Mouseover'd)
the way that I am approaching it at the movement (Not the best way and by far not the coolest way. Is to have a list of all the dates in a column next to the Line chart and using some VBA, what ever date is selected in the column the pie chart displays the corresponding data. But eh challenge is that when there is alot of dates, I am going to be scrolling up and down.
I am using Excel 2010, but I cant not use the slicer's as the other users do not have 2010, they have 2007.
View 1 Replies
View Related
Dec 29, 2011
I have a spreadsheet created in Excel 2003 (which is what we use at work, unfortunately).
My employees periodically take a test to ensure they have certain items memorized (or are making progress to that end). The spreadsheet rows show all 46 of my employees, and their test scores. The columns are the dates that the tests are administered. I can create a line graph based on the chart data, and interpolate these data with no problems.
The problem is that there are 46 employees! 46 lines on the same graph make for a very cluttered, hard to understand visual. I want to simplify the view by "filtering out" some of the data.
I have an additional column in my spreadsheet for each employee's work area (Area 1, Area 2, etc), and another column with data based on first letter of last name (the values here could be "A-G", "H-M", "N-S" and "T-Z", for example). I figure i could filter my line graph based on these two columns. For example, somehow select just Area 1, and reduce the number of lines on the graph to 16. Or better yet, Choose "Area 2" AND "A-M" and end up with 7 employees (and therefore 7 lines on the graph).
Here's what i have tried:
1) Select the work area column, and use the Filter, which created a drop-down list at the column heading. When i use this drop-down list, i can easily filter the data in the worksheet by Work Area, but this is not reflected in the line graph, which still shows all 46 lines. The problem was that i forgot that i had set the Calculation Options to "Manual". Setting this to "Automatic" (or leaving it on Manual and pressing F9) solved the problem, as the chart now updates when i use the filters. Calculation options are under the "Formula" tab in 2007, or in Tools -> Options -> [either calculation or formula, i forget what it's called] in 2003.
2) Create several separate line graphs in several separate sheets. I wouldn't want to assign someone else the task of maintaining a spreadsheet of such inefficient design.
View 1 Replies
View Related
Mar 26, 2009
I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).
So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.
View 4 Replies
View Related
Jun 30, 2008
I have a chart with 3 data series. The series are located in columns A, B and C respectively. Series I is a general number anywhere from 0 to 100,000. Series 2 is also a number, but is is devided by series 1. So, if series 1 was 100,000 then series two would be 25,000/100,000 which is 0.25. Series three will always be numerator of series 2 or 25,000 in this example. The numerator, or the 25,000 will never change, so, in my example, series three will be a straight line across the chart because it is always 25,000.
In my example, the .25 is plotted on the left value axes, and series one is plotted on the right value acccess (secondary axis). Series three is just a line in the middle.
My problem is that sometimes the line, series 3, doesn't match the values in both the right and left values axes. So, if series one is 48,000, then series two would be 48,000/48,000 = 1, and series three would be 48000 - the straight line. The third series should be a line touching the 48,000 on the right and the number 1 on the left. It does touch the 48000 on the left, but is below the number 1 on the left.
View 9 Replies
View Related
Nov 1, 2006
I am trying to graph the following data in an area chart with line charts superimposed on the area chart. I have a lot of data (and a lot going on) so I'm trying to figure out the best way to show this in excel from a functional standpoing (i can't get this to work in excel!!) to also an asthetic standpoint (dont want it to look terrible or illegible). This is what I'm trying to chart:
1) Weather data (temperature) by region:
So one region, would be: Northwest
I would like the "area" (so a shaded region) to be the min/max of the temperature data for each month.
2) I would like to show the temperature for each year as a line graph on the chart - so you can see if a year falls in or out of the shaded region.
3) I would like to show a company's sales increases across the same months per year as separate line charts. I may choose to just show the biggest outlier year in the end... or to show 2006 (the latest data).
What I am trying to convery with the chart is that the company's sales is or is not tied to weather deviations. I have attached an excel file with the data. I haven't been able to use the area chart or get a two axis chart to work or get it to look even remotely professional.
View 3 Replies
View Related
Jul 12, 2013
How do I paste the first chart into the second chart but maintain the format of the second chart?
View 2 Replies
View Related
Apr 22, 2014
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
I am using Excel 2010
View 2 Replies
View Related
Apr 14, 2008
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
View 3 Replies
View Related
Apr 28, 2009
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.
View 4 Replies
View Related
Nov 1, 2011
I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?
View 2 Replies
View Related
Apr 29, 2014
I have a file with a small example of sales and % of sales for about 7 persons.
I wanted to insert a pie chart but want to show labels inside the pie chart along with % of sales and the sales person name near each part. How to do this ?
I have attached a file : Book1.xlsx‎
View 4 Replies
View Related
Jul 29, 2014
I have some cost data for 5 different technologies that I would like to plot in a graph. the technologies belong to 3 unique categories and I want to group them on the chart so that there is no gap between technologies in a same category.
I have a graph where they are grouped by title of the category but how I may remove the gaps between the individual bars in the same category?
View 4 Replies
View Related
Jan 30, 2014
make it working, tried from online thread but unable to get it done.Dynamic.xlsx
View 5 Replies
View Related
Jan 18, 2009
I am coming across an error when i am trying to make my final chart. I am trying to chart on ten pieces of data, but for some reason when i complete the chart...it is only reporting on 7 out of the 10. if i try to do it manually, after i have selected my 7th piece of data, it will not let me select any more.
find attached a copy of my report. Hopefully it does not look too confusing. Note: Sheet "Agent Analysis" works fine...this may give you an idea of what i am trying to acomplish. It is sheet "AUX Breakdown" where i am having the issues.
View 2 Replies
View Related
Nov 11, 2011
how I can extract data from a non-linear line graph to be used in a calculation. The graph will be made based on user input data and the values to be obtained will not be entered, but read off the graph. Again, it is non-linear, and while it could be log-normal data, it may not be any particular function. Basically, I want the program to read off an x-value for a particular set of defined y-values.
View 1 Replies
View Related
