I am trying to make a line plot for traffic data. I want to make 37 different line plots with two series (count 1, count 2) and 72 values and the name of the trafficlight. I attached a text file with data for one of the 37 trafficlights. In my datafile, below the first 72 values the values for the next plot are written. I can make this tables by hand, but I prefer a macro because I want to repeat the exercise for different datafiles.
View 5 Replies
My chart is coming in blank when I set up a line chart. This is my data that I selected and then pressed chart. 1997
63.9%
1998
63.2%
[Code].....
I am trying to create a line graph to show the trends (up or down) of I-Fund vs Gold. The trouble I am having is how to set the axis. The date one is an easy one, but the gold range is more or less 800-1000, and the I-Fund is 13-18. How can I have these both on the same graph to compare? I am attaching an Excel sheet as a reference. Obviously I need to delete the empty IFund cells.
View 2 Replies View RelatedI have a line Chart and want to create a static horizontal/indicator line.
I cannot create a new series to plot this horizontal line as I'm using an external data source (OLAP cube) as source data for the chart.
I am trying to create a dynamic Excel line chart.
All is well, except for my x-axis (which is a text axis). It currently looks like this: Dynamic axis.jpg
I am using the Offset formula. The formula for my x-axis is =OFFSET(Worksheet1!$D$4,0,-3,COUNT(Worksheet1!$D$4:$D$36)). This is to show the axis if cells in Column D are filled up appropriately.
However, in the cells containing the axis name itself, this formula is used =IF(D9=0,NA(),'Worksheet2'!A6). I've tried removing the formula from these cell, but that didn't solve the problem.
How I could create a chart (standard column) with an average line in Excel 2010?
View 4 Replies View RelatedI have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
i hv following code
(i use generate macro)
my question is how to arrange the code from one line to multiple like :-
following code show in excel macro environment is one striaght line.
' Create new var on yr , and replace 2006 to CY06.
ActiveCell.FormulaR1C1 = _
I'd like to create a simple graph exactly like the one on the picture, but for the life of mehow to do this on Excel 2010.This is the data I have:
Seperation First target Second target
0 59.00% 0.00%
2 64.28% 40.62%
4 62.68% 48.99%
6 60.81% 65.83%
8 61.82% 70.21%
I am trying to make a line graph showing the gradual rises and falls in profit over a period of time, when I use the data and click line graph normally, it will plot each bit of data individually rather than gradually, for example;
13-Sep-13GBP 1,107.57
18-Sep-13GBP 6,432.74
21-Sep-13-GBP 477.71
22-Sep-13GBP 19,664.65
23-Sep-13GBP 1,604.88
If I tried to plot the above data, the graph will not show a gradual rise but instead will show a value of £1604.88 on the 23rd of September when instead I would want the chart line to be at £29,287.55 (The total).
I'm currently drawing rectangle shapes with transparency on top of a graph to highlight certain depth ranges in vba. Rectangles span entire graph width. Is it at all possible to draw the shapes behind the series plots?
View 7 Replies View RelatedI would like to use multiple scatter plots and bar charts in one chart. Problem is that X-Axis goes horribly wrong every time, even if I have same points on both scatter and bar. Here is Data for 2 Scatter Plots
XScatter1
1,43 0,14
2,60 0,25
3,04 0,37
3,60 0,50
3,93 0,60
4,69 0,80
X Scatter2
5,10 0,92
5,36 1,00
6,43 1,26
7,60 1,53
8,19 1,65
8,79 1,77
9,69 1,93
And here is data for 2 Bar Charts
XBar Chart1Bar Chart2
1,43#N/A 0,00
2,60#N/A 0,01
3,040,01 0,01
3,60#N/A 0,01
3,93#N/A 0,01
4,69#N/A 0,00
5,100,02 0,01
5,36#N/A 0,01
6,43#N/A 0,02
7,600,04 0,02
8,19#N/A 0,01
8,790,11 0,02
9,69#N/A 0,01
I start with Scatterplot and put those in the graph, then I add other data and change it to Bar Chart, but this is the point where X-Axis gets mixed up and scatter plot X data and Bar chart X data are not matching.
I have a workbook that has a 50 years worth of daily values for ~30 data streams. Each data stream is a tab, days are in rows with each year a new column. I want to plot selected date ranges of selected data streams from selected years on one page. So I have a tab, "QuickCharts" that has a spinner to change the year and based on that change it pulls in two dozen different data streams (reservoir information, so we're talking Inflow, Outflow, Stage, Temperature, etc at different sites). I have static plot of the entire year's data for each site. This much works fine.
I want to be able to dynamically change the plot ranges, though, with spinners. I created 12 named ranges using this type of syntax:
Code:
=OFFSET(QuickCharts!$Y$3:$AP$367,PlotStart,9,PlotStart+PlotOffset,1)
where PlotStart and PlotOffset are named cells containing a single value.
Things that work:
- I can perform operations on the named range (e.g. =Average(PR_XAxis) works fine)
- I can go to the named range dropdown in the upper left and type the range names in, and the range is selected as expected.
- The named ranges are offered as autocomplete options when typing in cell formulas.
Things that don't work:
- These named ranges don't appear in the named range dropdown list at the top left - I can type them in, but they aren't there by themselves.
- I cannot use those named ranges as series for a chart.
I tried replacing PlotStart PlotOffset with the absoilute cell references. That didn't work.
I tried scoping to worksheet instead of workbook - didn't work.
This seems to be related to this question, but the solution there isn't working for me. Selecting Chart Data Sources Using Named Ranges...
I would like to find the intersection points of all of the linesshown on the included chart. If that could be output into a table, by the day, that would be ultimately what I am after. http://bladeseri.50megs.com/Intersects.png. Guess 50megs.com doesn't allow this type of reference. Here is Geocities link http://www.geocities.com/bladex2007/Intersects.png
View 5 Replies View RelatedI would like to build a graph that compares 2 sets of data but only plots 1 dot where the two intersect. I have seen this graph made by others in Excel but I do not know what it is called or how to make it. An example data set is below.
ID Age Height (cm)
1 5 125
2 7 140
3 8 138
4 11 152
I would just like to plot the location where the age and height for the ID's cross and not 4 plots with the age and 4 plots with the height.
I get large data sets that are organized in columns and each has a title at the top of it. I will get several sets of this data in order to compare them all on graphs.
Now here is the kicker, the columns are not always in the same order. Not always named the same. And not always the same number of columns.
I want to make a tool to handle this to a point where I just import the data and everything is taken care of. I also want a printable report for each data set to which I can select from a drop down menu. Populate cells on a tab, and then can be printed.
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
The attached file works with 3 Drawers and 3 Doors but if I use 2 Drawers and 2 Doors the Borders do not line up. I think the Drawers are right the Doors appears to be the problem.
Same thing happens with 3 drawers and 4 Doors. It has and extra border on the right side.
See Attached : ozgrid cabinet error.xlsm
Also I would like for the Measurements to be in the cell to the right of the left hand border and center across each of the sections.
Change B3 and B4 to 2 then click draw. 3 and 3 work 3 and 4 don't but 4 and 4 does.???
I got to the point where the macro is doing (more or less) what I want.
I now need to run the macro for each line in column C.
Basically my macro is creating 9 lines in column E getting as input C1 only (for the time being).
How do I say to my Macro, please run in E, but the first time you run get as
input value C1, the second time C2 and so on until I've got values in C?
I've looked into the loop documentation (for and foreach) and overall it makes sense, but how to refer each time to the next line in C?
I've download the CSV files from my bank. Somehow I thought they'd import painlessly. I dunno what's happened, these files are bloody ridiculous to look at.
Somehow I've ended up w/ Date, then Amount in the cell under it, then a description of what I bought in the cell below, and then something to the cell to the right of it (a further description sometimes, at other times nothing).
I have about 1500 lines of this so doing this automatically is not in question. I want to figure out how to run a macro to organize this. The logic is fairly simple. Where-ever the cursor is, copy the stuff in there over 2 lines, go back to original starting point, copy next cell, paste it next to where i previous pasted, goto third line, copy, paste next to second place i pasted, and then goto next set of data/dates. So I did that once figuring it could keep repeating the cycle. NOOOO that didnt happen. I did this for one set and then just stopped. Everytime I run the macro it deals w/ the same data. For example I created the macro to start of w/ cells A2, A3, and A4, then goto A6 and stop. I figured once there I could run the macro again, and it would do the same stuff for A4, A5, and A6 that it did for the previous one. Nope, it went back to A1-3.
Of course if someone can give a better idea of how to work w/ CSV in the first place that would also help. It just never made the colums in the right place. I got to choose the variables that would decide when a new column starts but I was never really getting the result I wanted. If this macro works though I wont need to work w/ that anymore.
Here's a sample though of how my CSV looks at the moment. These are three entries. The first one's description takes up one cell, the next 2 have 2 cells each.
D06/13/2008
T-20.00
MABM WITHDRAWAL
^
D06/16/2008
T-26.25
MMISCELLANEOUS PAYMENTS Good Life
^
D06/16/2008
T-25.00
MPOS MERCHANDISE AMC MISSISSAUGA
I am dealing with data sets from various instruments that have different sample rates. I am deleting data points I don't need from some of the sets with higher sample rates so that all the data is on the same time scale.
The macro I have is super simple, but incredibly slow. I'm simply deleting every other cell down a column.
VB:
Sub OATcondense()
Application.ScreenUpdating = False
Do While ActiveCell <> ""
ActiveCell.Offset(1, 0).Delete Shift:=xlUp
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub
I have created this macro below however if on the worksheet I insert a line or lines the macro range G25:G107 does not change - it remains static.
How can I get an insert of a line to increase the macro range by the number of inserted lines. For example if I insert 2 lines then the macro range s/b now G25:G109.
I have 2 tabs on my spreadsheet. The first has some text at the top in cells A1 and A2, and that's it. The second tab I need a script for. Basically, I need some sort of macro button and a text box. I want people to be able to type some text in a cell or box on the 2nd tab, hit a button, and the text that has just been typed will be copied and pasted in to the next available cell down on the first tab.
For example, say the 1st tab had 'title' written in cell A1, and 'hello' written in cell A2. If I were to go to the 2nd tab, type 'bonjour' in to a cell or text box, and hit a button, the text 'bonjour' would be copied, the script would read the 1st tab and see that cells A1 and A2 already had text in them, and so paste 'bonjour' in to cell A3.
I am in a process of creating a excel based productivity for my team. This workbook has 2 worksheet, one is QA productivity tracker which is accessible to all, after the information is filled i want to store it daywise in a master tracker (next worksheet).
I have created a macro which copies the information to master sheet, but the challenge i am facing is that at every execution, macro should paste the data in next row.
I am using a excel macro to send message from excel and i have the code to send the mail, but the body of the mail is coming in a single line but i need to send it in different lines, how to do that.
View 3 Replies View Related I have a range of data (not a named range, just a selection) and I typically want:
- the top 2 rows to have a heavy border on the left, top and right sides with a light line on the bottom.
- On the bottom line, I need a heavy line on the left, bottom and right sides.
- The body in between needs to have heavy lines on the left and right sides with dots for all in between.
The top 2 lines show titles, the bottom line is for totals and the body in between shows the data.
I've made 3 separate macros that do each part but I'm hoping that they can be integrated into one.
I have two sheets that i am working with sheet1 and sheet2.
I need a macro that will insert multiple rows in row 8 of sheet 1.
I also need it to copy the formula from row 8 sheet1 to every new row inserted.
the number of rows inserted will depend on colum A from sheet2.
*column A contains numbers, and the amount of numbers will vary. The data starts in A11 and continues down.
So for example. if sheet 2 column A11 and A12 and A13 are the only cells that contain numbers, then the macro will add 3 lines starting in row 8 of sheet1 and it will also copy the formulas from that row to every new row inserted.
I have the following code which reads line 54 (row 54) in a spreadsheet and calculates the following using data in subsequent columns. Sometimes the data I need isn't always in row 54, I would rather have the macro read the data in Column A[AB] that is on line 54...sometimes on 53.
The date is Sep-09. How can I make it read this rather than AB54. I think I just need changes @ the top (ie Dim rng as Range, Set rng = [AB54]).
Sub Profile_info2()
Dim rng As Range
Set rng = [AB54]
rng.FormulaR1C1 = "=RC[-12]+R[-1]C[-12]+R[-2]C[-12]"
With rng.Offset(0, 2)
.FormulaR1C1 = "=((RC[-28]+R[-1]C[-28]+R[-2]C[-28])*4)/((RC[-25]+R[-1]C[-25]+R[-2]C[-25])/3)"
.NumberFormat = "0.0"
End With
With rng.Offset(0, 3)
.FormulaR1C1 = _
"=((RC[-15]-RC[-29])+(R[-1]C[-15]-R[-1]C[-29])+(R[-2]C[-15]-R[-2]C[-29]))/(RC[-15]+R[-1]C[-15]+R[-2]C[-15])"
.Style = "Percent"
.NumberFormat = "0.0%"
End With
rng.Offset(0, 4).FormulaR1C1 = "=RC[-8]"
With rng.Offset(0, 5)
.FormulaR1C1 = "=(((RC[-17]-RC[-31])+(R[-1]C[-17]-R[-1]C[-31])+(R[-2]C[-17]-R[-2]C[-31]))*4)/((RC[-28]+R[-1]C[-28]+R[-2]C[-28])/3)"
.Style = "Percent"
End With
End Sub
If I number my lines of code and an error occurs, the use of Erl in the VBA will then return the exact VBA line number that the error occurred on. Is it possible to automatically capture or record that same effect when the macro process leaves its current sub and goes to another?
Reason: I'm trying to create a Call Stack that can be reported through an Error Handler that will include the exact location and process that the error occurred to better troubleshoot and understand where and why the error occurred.
The best I can come up with so far is manually putting in bookmarks along way so I know how far along the macro went before the error. From all of my searching I believe retrieving the Excel Call Stack is not possible and so one must be manually created.
Enclosed is an example of what I have so far. It goes through several macros and logs the Call Stack. It’s a work in progress so it is a little sloppy looking but it is functional. If a Sub finishes it is then taken out of the Call stack.
Several "BookMarks" are placed to give an idea of how far along the Macro has gone within that Sub. The Code for the Erl example is:
Sub SampleErrorWithLineNumbers()
Dim dblNum As Double
10 On Error Goto PROC_ERR
' Errors if table doesn't exist
20 Select Case Rnd()
Case Is < 0.2
30 dblNum = 5 / 0
40 Case Is < 0.4
50 dblNum = 5 / 0
60 Case Is < 0.6
70 dblNum = 5 / 0
80 Case Is < 0.8
90 dblNum = 5 / 0
100 Case Else
End Select
110 Exit Sub
PROC_ERR:
120 MsgBox "Error Line: " & Erl & vbCrLf & vbCrLf & _
"Error: (" & Err.Number & ") " & Err.Description, vbCritical
End Sub
