Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default.
the script is like this :
ActiveSheet.ChartObjects("Chart 31").Activate
Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create A Graph Report
Rep NameCall StartedDuration h:mm:ssRep 12:19:18 PM0:00:36Rep 13:09:01 PM0:00:56Rep 13:11:01 PM0:01:05Rep 13:12:12 PM0:01:13Rep 13:26:26 PM0:00:39Rep 27:08:35 AM0:01:57Rep 27:16:32 AM0:02:12Rep 21:47:17 PM0:02:01Rep 37:05:01 AM0:10:37Rep 37:36:24 AM0:00:16Rep 311:41:43 AM0:15:21Rep 312:47:36 PM0:00:46Rep 31:45:36 PM0:02:19Rep 31:49:54 PM0:00:32Rep 31:51:46 PM0:00:56Rep 31:55:03 PM0:00:35Rep 31:59:26 PM0:00:22Rep 47:04:24 AM0:00:16Rep 42:40:41 PM0:00:08Rep 43:06:22 PM0:02:16Rep 43:21:35 PM0:00:04Rep 43:21:49 PM0:03:05 which is the time that any rep started a call and the duration of each call and i need to make a graph from it that kind of looks like this: (not actual data) this way you can clearly see that for the most part rep 3 was the most productive however only rep 2 was on a call after 2pm. i need this report to see tendencies on how much time is wasted on meetings and such keeping the reps off the phone.
View Replies!
View Related
Create Chart/Graph From Text Strings
Can I build/present a "Pie" chart DIRECTLY from a list of "ABC" letters WITHOUT counting the occurrence of each one of them and using the results [nor VBA / nor Pivot-Table] - which is the trivial way of building charts !? An example of such a list will be: A A A B B C C C C C
View Replies!
View Related
Create Stacked Bar Chart/Graph
I need to plot a stacked chart. I have attached a sample of it. I need to plot, category As X and subcategory and and its percentage as Y (stacked chart). In the attached sample I plotted a chart. The problem is, the series name showing is not correct. It takes the series name from the top row.
View Replies!
View Related
Creating A Graph Using VBA??
I have implemented a function in VBA which calculates certain values, called 'IndexSim.' These values are not required to be outputted into the spreadsheet, however I would like to create a simple line graph plot of these values against 1,2,....etc however many values are chosen to be created. The code is as follows: 'Create storage for FTSE100 and binary payoff simulations. Redim IndexSim(1 To SampleSz) As Double Redim Payoff(1 To SampleSz) As Double 'Do the simulations. For Cnt = 1 To SampleSz IndexSim(Cnt) = gBmProcess(Kt, r, q, Vol, TMat - TNow) If IndexSim(Cnt) >= IndexBetValue Then Payoff(Cnt) = Range("D18").Value Else Payoff(Cnt) = Range("D19").Value End If Next Cnt how I can create a line graph for the 'IndexSim' values, which will update everytime the macro button is clicked.
View Replies!
View Related
VBA: Don't Want To Graph Empty Cells
I am making a program that several people are going to be inputting data into. I selected a huge range to ge graphed, so people could simply add their data and it would atomatically appear on the chart. The problem is, that I don't know how much data I am going to get in total. I want to select a range, but only have it appear on the chart if the cells are not empty (I don't want a ton of empty slots on my chart). This is the basic idea I was trying to work with even though this particular one is not correct. I am trying to set my range only equal to the cells that do not = 0. Sheets("Graphs").Select ActiveSheet.ChartObjects("Chart 12").Activate Set graphdata = (Range("F30:IT31") <> "") ActiveChart.SetSourceData Source:=Sheets("Data").Range(graphdata), PlotBy _ :=xlRows ActiveChart.ChartArea.Select
View Replies!
View Related
Create 'blank' On Graph Instead Of 0 Value?
What can I have the IF statement return as a result that will NOT graph at all. Just leave a hole in the graph. Long Question: I have a large range of values and dates. 1/1 1/2 1/3 ....etc 10 11 7 .......etc 8 12 6 .....etc These values are pulled via VLOOKUP() from various places. When VLOOKUP finds a blank cell, it returns a '0', which doesn't work for me. I've added an IF(ISBLANK), to return "", which is (I believe) an empty cell. The problem is, this is still graphing as a zero. So it will be graphing along nicely, and then shoot down to zero and back up again.
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Make A Graph That Will Graph Against Time
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 Replies!
View Related
Line Graph: (line Graph To Display An Amount Over Time)
I want to use a line graph to display an amount over time - that's the easy part. On the other hand, I would like to have to group the lines based on a value. A short example: Imagine you own 3 different stores and you're selling oranges. So your table looks like this: http://img179.imageshack.us/my.php?image=orangeshm4.jpg Now I'd like to have one graph (3 different graphs won't work as the rows increase -I need to select the whole column as data source): Date on the x axis, Oranges sold on the y axis, and one line per store (e.g. a green one for store A, a red one for B and a blue one of C, doesn't matter).
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
Vba To Create A New File From Spreadsheet
I have the following spreadsheet: A-------------B File No.-----PRODUCT TYPE 123456-----NDT 123456-----NDT 123456-----NDT 123456--------- 123456--------- 789110--------- 789110--------- 789110--------- What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456. End result A 789110 789110 789110
View Replies!
View Related
Create Outlook Appointment From VBA
I am probably overengineering my macro, but I would like to setup an outlook appointment in two peoples calendars from my userform. The macro has access to the email addresses of the selected persons, I just want to send date/time and subject.
View Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
Vba To Create Separate Reports From Master
I have the following spreadsheet Columns on master sorted by Column J: A--------------E-------------J SUPPLIER----CARRIER------LOC CODE BELGIUM-----HAM-----------BP HYDRO-------MSC-----------BP DAINIPPON---NNR-----------BP BELGIUM------YM-----------BR BELGIUM------YM-----------BR HYDRO-------MSC----------CA BELGIUM-----HAM-----------CA HYDRO-------MSC-----------CA HYDRO-------MSC-----------DE DAINIPPON---NNR-----------DE BELGIUM------YM-----------DE BELGIUM------YM-----------DE What I need too do based on the Codes in Column J is create a separate workbook for each different code.
View Replies!
View Related
VBA Macro To Create New Worksheet, Charts, Etc.
I need a macro to create the following worksheets and charts from an Excel data set: Three (3) worksheets (already created manually in attached Excel file): 1. Chart Data. (a) Column A in Chart Data is always numbered 1 - 600 (50 years x 12 mos/yr). (b) Column I and column Y data sets (from Prod_Month) created in Chart Data. Each data set can be identified and collated with column F in Prod_Month (API) which is unique for each dataset. 2. Rate vs. Month - plot of Daily Gas (col. Y in Prod_Month) vs. Months (col. A in Chart Data). 3. Rate vs. Time - plot of Daily Gas (col. Y in Prod_Month) vs. Calender Time (col. B et. al. in Chart Data) At a minimum, could someone help me create the Chart Data worksheet from the data in PROD_MONTH? Charting all the columns takes time from Chart Data but any data manipulation macro(s) help.
View Replies!
View Related
Can VBA Create A Named Sheet In One Step?
My vb code currently creates a new sheet via "Sheets.add". This new sheet has the default name of "sheet1" which I rename as "tempSheet". I then populate tempSheet with data, save as a CVS file and then I remove tempSheet. If I stay in my workbook and try to execute the code again, the default name of the sheet it now creates is "sheet2" and not "sheet1" - this is a problem becuase my code is trying to rename "sheet1" "tempSheet" and of course it cant.
View Replies!
View Related
VBA - Create Outlook Message With From-field
I have two question for Excel in combination with microsoft outlook: 1. I was wondering if it is possible to create an email from Excel which automatically adds the From-field with a pre-defined emailadres. I am using CreateObject("Outlook.Application"), but I can not add the From-field. 2. I also would like to know if it is possible to do automatically fill the To-field with the emailadress from the user who send me the original file. Maybe this needs some more explanation (sorry my english isnt too good): * A user can download an excel-form from the internet * The form is sent to my emailadress * I give a number to the form * The user will get a message that his question is attend to with the number of the question So I use a macro-button to reply when I have filled in the question-number.
View Replies!
View Related
VBA To Create A Hyperlink Drilldown For Each Cell
I need help in making a VBA to create a looping hyperlink drilldown for each cell in the inventory_general wksht. The purpose of this sheet is to allow a user to click on a hyperlink which will then reference a macro to autofilter the data in the bin_lot worksheet to display all rows for that particular item number. Apparently, you can't select all the cells and insert a hyperlink because all the hyperlinks will reference the first cell value for autofiltering. This is what I tried but it's not working obviously. Sub HyperlinkDrilldown() ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "Bin_Lot!A1", TextToDisplay:=target.value Range("A2:A21").Select End Sub
View Replies!
View Related
VBA - Create Folder With Date In Name, Populate With Sheets
I have a workbook that will have many modules (5) that will perform all sort of checks etc. The output for each module is a worksheet that i need to export to a new workbook named with the name of the original sheet and current date appended e.g. "Dbase 27 06 2008.xls". Every time any of the 5 modules is run a check is performed whether a folder with a specific name and date exists e.g. "Audit Tool 27 06 2008" and if it does not exist yet to create it. The new workbook should be saved to this folder with above mentioned name e.g. "Dbase 27 06 2008". I found some info on this topic but I just cannot assemble all pieces of code together (I am new to this).
View Replies!
View Related
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
Create A Named Range In VBA For Column A, Which Is A Sum Of B And C
I would like to create a named range in VBA for column A, which is a sum of B and C. The problem is that A can have 0 as a value. What I would really like to do is define the named range in column A as A2 to the last column with a value in column B(B10) which would make the named range in column a A2:A10. ABC 61555560 00 1250120050 725725 00 850850 1919 7216720016 995995....
View Replies!
View Related
VBA To Create Charts/Graphs From Non Continuous Ranges
I have an excel sheet populated with loads of values. There is a space between each set of data. I need to draw a graph for each section and i cant work out how to do it. I have attached the sheet in question. The gap between each column has "space" written on row, which is needed to prevent it being deleted. The drawing of the graphs needs to be automated as there is going to be 100's of sheets containg lots of data. The column size of each section can change so it needs to be dynamic. I have attached an example sheet
View Replies!
View Related
Create A Single Range From Multiple Ranges For VBA Input
I have a VBA function with the header: cubspline(Xval As Double, XRange As range, YRange As Range) As Double The problem is that XRange (and also YRange) is in different areas of the spreadsheet. I want to combine these areas into one range which I can pass on to the cubspline function. What is the easiest way of doing it? I'm looking for something like a "union" function for ranges in Excel.
View Replies!
View Related
VBA To Create Word File And Add A Custom Property To It
I have an Excel sheet with a long list of data. A short example is shown below: Section | Title | Item 1 | INTRODUCTION | a 1.1 | title2 | b 1.2 | title3 | c 1.2.1 | title4 | d 1.2.2 | title5 | e I made a VBA macro in Excel that runs through this list and creates a new Word file for each item. The filename of the document is based on the data in the Excel file (section and title). Now I would like to add a custom property to each of the newly created Word files, i.e. the value in the 'item' column. Does anyone of you know how I should do this? Or should it be better if I write a macro in Word that runs through the Excel data to create the word files? Here is the code I use to generate the word files:
View Replies!
View Related
Create SQL Query In VBA And Display Result As Table In Worksheet
I need the VBA script in excel that will do a SQL Query, for this case I need to select a value where there are 2-3 tags and between certain period then display the result as a table in a worksheet. Example I have a database with 3 types of tags "Tag A", "Tag B", and "Tag C", and each tag have a value with different timestamp. the database looks like this: No. Timestamp Tags Value 1 1-Jan-2010 Tag A 18 2 1-Jan-2010 Tag C 20 3 2-Jan-2010 Tag A 20 4 3-Jan-2010 Tag B 17 5 3-Jan-2010 Tag C 19 6 4-Jan-2010 Tag B 18 7 4-Jan-2010 Tag A 20 8 5-Jan-2010 Tag A 22 9 5-Jan-2010 Tag B 18 10 5-JAn-2010 Tag C 20
View Replies!
View Related
VBA Macro To Create & Format Text Box On Worksheet
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported. Sub textmove() Dim bname As String Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _ 230.25, 120#).Name = "client" bname = Sheets("data").Range("a3").Value Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname With Selection.Characters(Start:=1, Length:=17).Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = xlAutomatic End With End Sub
View Replies!
View Related
VBA Macro To Create Multiple Data Validation Lists From Variables & Named Ranges
I cant seem to find the correct syntax for creating 14 validation lists using array members as the source of the named ranged. The validation lists are stored on a different worksheet, the Named Ranges are created fine, as are the ranges that are having the validation applied. The Syntax I am having a problem with is Public Sub assignDVList(WSD As Worksheet, sListName As String) Dim DVListName As String DVListName = "DV" & sListName Application.Goto Reference:=sListName With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & DVListName It is the Formula1:="=" & DVListName that is creating the headache. The sub is called as the array moves through the columns, using the header row as the Name for the Named Range, and the data Validation worksheet uses the same naming except it has DV in front.
View Replies!
View Related
Create A Chart To Create Totals Based Upon Different Keys
Trying to create an excel chart to create totals based upon different keys. I need to be able to calculate how many customers there are by Manager and then By Rep. Then to figure out how many were New, Current, Total # of RSVP and attended for that Rep. Below is how I have started but I am having some problems getting certain parts. I know when I get one the rest will fall into place. I can calculate how many total customers by manager and by rep just by doing a Countif command but how do I determine the # of New, Current etc. Is there a If Than command? Managers Totals are simply his reps totals. Example.. A B C D E F G 1 Manager Rep Customer New Biz Current # RSVP # Actual Attend Need totals to look something like this.... A B C D E F G 1 # of Cust # New # Current #RSVP # Actual Attend 2 Manager 3 Rep 1 4 Rep 2
View Replies!
View Related
Vba Error Run-time 429 Activex Component Can't Create Object - Excel & Outlook 2007
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error: Run-time error '429': ActiveX component can't create object and is stopping on the line Set OutApp = GetObject(, "Outlook.Application") FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error. Dim OutApp As Object Dim OutMail As Object Dim SigString As String Dim Signature As String 'Set OutApp = CreateObject("Outlook.Application") Set OutApp = GetObject(, "Outlook.Application") If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0)
View Replies!
View Related
|
TRACKER
