Automated Graph Build Macros
Jun 13, 2014
My problem is that i have to create 100+ graphics (this my boss want),so after i create the first table i just copy past and change the data range and the name of the table. I want to automate this process with macros so i record a macros .
Explaining: "Eono1 is the name om my workbook.
So what i want ,the macros automaticali create new graph ,and change the data range ,(my data range is for january to december i row so i want to grab the next row from C3:N3 to become C4:N4)
Lets say i have 48 countrys (names R3C2),macros should automatically stop after c48:N48 for 2014,q48:ab48-2013 and AE48:AP48.(The last graphic is created).
Here is the macros and the exaple table with chart
Code: [URL] .......
Code:
Sub macro()
Range("B2:N3").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Eeno1!$B$2:$N$3") -----this one unchanged
[Code] ......
View 2 Replies
ADVERTISEMENT
Jun 23, 2014
I have been trying to create a macro that would create a schedule for me based on date and a product type. In columns K-N I have an IF statement that tells when a shipment would need to arrive in a row that corresponds with a date (column A is 1/1/2015 - 12/31/2015). On the next page I would like to generate a schedule that lists the dates that all of the products are needed in chronological order. I've tried to use custom functions like finding the nth_occurence but it just gets way too messy with so many shipments.
View 6 Replies
View Related
Apr 17, 2014
I 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.
View 1 Replies
View Related
Jun 5, 2008
Im working on a graph that uses the code below to plot the series
Sub AddDataToMyChart( Name As Range, XData As Range, YData As Range)
With ActiveChart
With .SeriesCollection.NewSeries
.XValues = XData
.Values = YData
.Name = Name
End With
End With
End Sub
Sub Test()
Dim lngTopRow As Long
Dim lngBottomrow As Long
Dim lngCol As Long
lngTopRow = 3
lngBottomrow = 8
ActiveSheet.ChartObjects(1).Activate
With ActiveSheet
For lngCol = Range("D2").Column To Range("h2").Column Step 2
AddDataToMyChart .Cells(2, lngCol), .Range(.Cells(lngTopRow, lngCol), .Cells(lngBottomrow, lngCol)), _
.Range(.Cells(lngTopRow, lngCol + 1), .Cells(lngBottomrow, lngCol + 1))
Next
End With
End Sub
...
View 4 Replies
View Related
Mar 21, 2014
I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
View 7 Replies
View Related
Jun 11, 2006
How can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500
1/3/2000 $600
3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
View 2 Replies
View Related
Jan 8, 2009
I have a brand Master in below format in sheet 1
brand codebrand Description10a11b12c13d14e
and Product master in sheet 2
product codeProduct Description000roof paint001floor paint002exterior paint003marine paint004wood finishes
I need in sheet 3 for each brand
Brcode + Product CodeBrand Description and Product description10000a roof paint10001a floor paint10002a exterior paint10003a marine paint10004a wood finishes
View 9 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Oct 20, 2008
I have a large excel sheet that will take a lot of time grouping it manually, so I was wondering if there was some way somebody could help me out with creating a code in VB to automate this grouping for me. All I would need is something to read through the rows of one column and when it finds a blank row, keep scanning until the next blank row and then group the data between the two blank rows. I have attached a small portion of the excel sheet I am working with if you need to get a visual of what I am needing.
View 9 Replies
View Related
Jun 19, 2009
I have a monthly report that I get with lab tests and amounts ordered. (I will copy a section for you to see). The Director wants it to be compiled into an easy to read, 1 test with totals. The way i get the data is broken down by site (we have 5). So, i may have between 1-5 rows of the same test with a total for each. I want to set up a Macro/Script that would go through the report and automatically add the same test codes together. My long term goal is to have it pull the Test Number, Test Name and Total Count into a separate worksheet, with the Total Count being the count of all the same tests.
see attached data: ...
View 9 Replies
View Related
May 17, 2014
how to build a formula that would allow my remaining UOM and it's associated cells to move up to the days remaining UOM once my time goes below, say 31 days.
View 3 Replies
View Related
Dec 4, 2009
I need to create a calculator that tells me how long a invoice will take to be paid using my current processes at work, working days only.
I need to imput the date the invoice is received and then for the rest to be worked out automaticly
I need it to do the follwoing ....
View 11 Replies
View Related
Jul 15, 2014
The code im stuck on is as below:
I am getting "runtime error 438. Object doesn't support this property or method".
[Code] ..........
So I have a formatted empty template listed as BUILD, It has two main parts, a left half that serves as a key and a right part that I want to populate with data.
So the first part of the code copies the key/margin then as I commented I want to start building sets for.
The second part of the code I'm trying to run a loop through user entered data stored in listbox2 grabbing each entry and pasting it in the first row of each set.
View 2 Replies
View Related
Dec 13, 2007
Is it possible to build a simple input dialog box with only code?
I'd really like to avoid having a userform permanently in the project.
Ideally it would either display and be dismissed with code only, or be constructed as a real userform, displayed and dismissed, then deleted from the project afterwards.
I am aware of excel's built-in input dialog box, but I'd really like to get away from it's awkward keyboard functionality if it's somehow possible to do. Although that would be ideal if there were some way to make the textbox's behavior to the Home, End, Tab and arrow keys more like that of a normal textbox, but I am unaware of any such options.
View 9 Replies
View Related
May 30, 2007
I am building a table for a group of vlookups, and I have a bunch of variables that need to be defined. I need to identify each and every possible combination of about seven different terms.
E.g. this simple table:
A B C
1 red1 org1 yel1
2 red2 org2 yel2
leads to this list:
red1org1yel1
red2org2yel2
red1org2yel1
red2org1yel1
red1org2yel2
...
etc.
This model is almost exactly what I am starting with, except there will be six or seven columns, depending. Also, no two variables within a given column will mix with one another, e.g. no "red1red2yel1" etc.
Rather than do this by hand, I thought there must be a programmatic solution (or even a little formula).
View 9 Replies
View Related
May 28, 2009
I need to build up one dynamic report.
Here is the task:
1) Everyday workers fill up with records in each sheet (record count can be different, because there is a "List" table)
2) Each day is in one separate sheet (for example: 1st June is in sheet with name 1, 2nd June is in sheet with name 2 .)
3) ALL sheets are named 1, 2, 3 ... 31
4) And in the sheet with name "ALL records" must be generated all records from sheet 1,2, ... 31
Here is my Example
View 9 Replies
View Related
Nov 12, 2009
I'm trying to build a rota for work, with the aim to gradually make it more and more complex.
So:
First step - I have made a basic skeleton, Names going down, Monday with beginning and finish, Tuesday with beg and fin etc to Friday going across. With hours worked during the day being calculated, minus 30 mins for lunch.
=(24*SUM((D5-C5))-0.5)
Second step - Now, for instance if you begin or finish work between the hours of 11:00 and 14:00 I need to make it so that you don't loose that half an hour.
View 9 Replies
View Related
Sep 7, 2009
I have a spreadsheet that deals with expiration dates. The whole sheet is conditional formatted so that when the current date is within 3days of the date showing in A2 then that cell changes colors. Ok is it possible that a script can be written so that when the cell changes colors... (Someone is going to think I am silly asking this but) excel sends an automated Email?
View 2 Replies
View Related
Oct 7, 2009
I m trying to achieve is generating ref numbers automatically.
I have attached an example.
In Cell A if the user choose yes it gives a ref number. The trouble im having is that say in cell A5 a Yes is choosen at a later date, it will throw the numbers out of order and the ref numbers get mixed up, there any way of stopping this from happening or any function that prevents this from happening
View 14 Replies
View Related
Jan 11, 2010
I am trying to write a formula which will consolidate notes for shipping orders
Column A Column B
Ship A 12/10 Talked to customer
Ship B 12/11 phoned cust
Ship C
Ship A 12/11 Called again
Ship D
Ship B
Ship A 12/12 Shipped
I want the notes in column B to be consolidated together so I have the history for each customer in one cell ie
Ship A 12/10 Talked to customer, 12/11 Called again, 12/12shipped
Ship B
The spreadsheet is really large so I want the formula to look up the customer name and consolidate notes automatically
View 7 Replies
View Related
Dec 29, 2008
im having trouble formatting the left header, im not sure how to go about it, ive recorded macros to see how they do because i dont need talking marks im buggering it up somewhere
Sub OCHeader()
Dim Sheet As String
Sheet = ActiveSheet.Name
With ActiveSheet.PageSetup
.LeftHeader = _
"&""Trebuchet MS,Bold""&11"
.LeftHeader = Sheet
End With
End Sub
View 2 Replies
View Related
Oct 1, 2009
I keep a spreadsheet with the 50 or so wireless phones in our "lab". When someone checks them out, the rules are that they change the cell under Available to "NO" and add their user ID. When they return the device, they change the Available cell back to "YES". What I want is a log of who checked out the device, and when... which would be kept in "sheet 2".
I attached a small spreadsheet with the same columns and information you would see in the regular spread sheet. Sorry, I would rather have put it here, but I couldn't figure out how to do that.
The spreadsheet has code which updates the "Counter" when someone checks out a device. Many thanks to the "Rocket Scientist" who helped me with that.
View 11 Replies
View Related
Nov 28, 2009
I am attempting to automatically (once a week) pull down the data from this site (http://www.dot.ca.gov/hq/esc/oe/plan...bidsum_csv.php) which is posted in a csv and parse it. While this may seem to be a simple process, the people who posted the CSV did not take into account that there are dollar values in the data in the millions and billions. As a result, a normal comma parse does not work since it splits the values.
View 5 Replies
View Related
Jul 18, 2013
Making an excel program that will automatically Email 5 people with either the whole document, a link to the doc, or just an email using the cells to inform them on what needs to be checked. And I don't even know where to start.
There will be a spreadsheet with everyone's name going down in column A, and then in the row next to the name it'll have a date of when they were put on it. It'll then have another date of the time they need to have done certain things, like see the doctor or get a class done, so when the current date reaches the date in the column the cell that is the same date will turn red, and the document will be mailed to 5 of the higher ups to inform them they need to check on this Marine to see if he has accomplished whatever task has expired.
Ive been trying to research a lot of this on my own, but ran into problem where the current outlook version does not suppose the command DATE within an excel, so I need to make a database using multiple excel sheets using Visual Basic(or so I'm told now).
View 1 Replies
View Related
Apr 5, 2007
I am having trouble trying to get an MS Access Query to run from MS Excel automatically.
I am trying to create an automated trigger in MS Excel that will automatically run my Access query by the times I specify in my statement.
For example: I need to run a query in Access at 9am, 12 pm and 10pm, that's it, but I believe I need to do it via Excel, I don't want the data returned to Excel, I just want Excel to execute the query at those specific times!
View 9 Replies
View Related
Jan 20, 2010
Is the anyway to have a macro wchich will automaticly open the IE browser when any of this word is clicked and search for this word in Google?
View 9 Replies
View Related
May 20, 2006
find the attached file there is a form which i have created to update a data sheet, there are some fields in the Sheet which are there is the form, but what i want is in the form once i enter the data in first 2 to 4 fields 7th and 8 th, 9th ,10 th fields should updated based on some formulas given for caluclations:
See attached file: i want the following fields to get updated automatically
1) Tripcode should get updated as
CONCATENATE of Cab type and trip type ( See Sheet "ATT" for formula i have mentioned)
2) Trip cost is based on trip if trip code is TTA then some value or if tripcode is TTB some value like that...
formula i have used in the sheet is...:-
IF(J3="TTA","750")+IF(J3="TTB","700")+IF(J3="TTC","650")+IF(J3="TSA","750")+IF(J3="TSB","700")+IF(J3="TSC","650")+IF(J3="TQA","750")+IF(J3="TQB","700")+IF(J3="TQC","650")
3) Extra Kms Cost is field Extra Kms * 5(Some Value)
4) total cost is :
Trip cost + extra Kms cost
View 9 Replies
View Related
Jun 15, 2006
I use daily an Excel workbook that comprises 6 sheets. The first sheet has some very clever vba which at the click of a button converts the data in it to an xml file. I have looked through this vba and have on occasions, modified it slightly, sometimes successfully, sometimes not!! This is what I am wanting to do...... At the moment my data is entered into a different sheet in the workbook. This is because there is also additional information on this sheet that is not required on sheet 1. When a row in this sheet is completed the part of the row that is required is copy and pasted to the next available row on sheet 1. I then go back to where it was copied from and fill that row in a different colour to show me that it has been 'processed'
Although Sheet 1 is capable of 500 rows (set in the vba), I always produce my xml file from it when I get 20 rows of data. So, what I would like to do is be able to click a button, or a key press to:- copy part of a row of sheet 2. paste that into the next available row in sheet 1 ( upto a max of 20 rows)change the complete row of the copied data in sheet 2 to a different colour (preferably 'fill')
View 2 Replies
View Related
Sep 1, 2006
I have to maintain changing data in a date of the month format. However, a portion of the data is on an A/B week basis. The data I am maintaining is static data (MAWB in the example below) that is hand-entered each Friday for the following week.
Detail: ....
View 9 Replies
View Related
Feb 6, 2007
I've got a list of 50,000 book titles in one column, with thousands of sets of between 2 to 5 duplicate titles. The second column has one unique summary per set of duplicate titles. I want to duplicate this summary to the remaining duplicate titles of the set (their 2nd column cells are blank). The third column describes the general condition of each book, and the fourth the asking price. I have been manually copying and pasting, but this is very time consuming. Is there a simple routine that can do this?
View 8 Replies
View Related
