I'm a VERY basic user of Excel. I have a mathematics and programming background, but no knowledge or experience in VBA programming.
I'm wishing to create a new function as a VBA custom function procedure to numerically intergate an expression (such as 3*X^3 + 2*X^2), defined by a worksheet formula (not by data points). The five parameters required by the function would be in the form of cell references which point to (1) the expression to be integrated (such as the above example), (2) the integrating variable (in this example, X), (3) the lower limit of X, (4) the upper limit of X, and (5) a tolerance value for termination.
I've been trying to implement the Trapezoidal Method - but not a flicker of light so far!!.
I understand the Formula property of a cell may be used to get the expression to be integrated into the VBA code as text. Then the Substitute function may be used to replace the variable of interest by a value, and the Evaluate method may be used to get the new value of the expression. I have no experience in implementing these steps.
I've made extensive attempts to do the above, but without success so far. ("I started off with nothing, and still have most of it left"!).
I'm hoping someone may be able to assist me, or else point me to a relevant area of the forum (or elsewhere), by demonstrating the methods and techniques needed to achieve the function I'm seeking. Perhaps it has all been done before, but I haven't located it.
I am using a macro to pull and arrange data from two worksheets into a "master" sheet. After I run the macro, I have a large set of data but no way to see which sheet each entry came from.
I would like to have a MATCH formula run in a column to the left of the data. Column A will indicate if the data line is present in the sheet A, and Column B will indicate if the data line is present in sheet B. In the past, I have used something like the following:
Things in brackets would be replaced with the relevant cell, sheet, and range being referenced. This line says "NO" if the cell string is not in the referenced range and "YES" if it is.
How would I insert this into the macro to have this run on every row with information in it?
I need to create a table of non numerical data that I can refer to in a formula, which will compare two pieces of data (both also inside this table), and based upon which is "higher up in the hierarchy" - the cell will display a certain result. Basically it will be an IF function using non numerical data that has a numerical value / "rank" attached.
Specifically - I need to allocate values to this list in cells A1:A5... EUR GBP AUD NZD USD ....where EUR is highest ranked and USD lowest.
Then in column B and C will be listed each of these values but in various combinations. In column D I will build simple IF functions to show the highest ranked in each combination. eg B1=EUR C1=GBP D1=IF(B1>C1,B1,C1)
What I am trying to do is have a drop down list in one cell and have the next cell bring in the correct price for the item in the first cell. Sounds fairly easy? Well both lists are 200 items.
I have been trying to do this with just three items so far. On sheet two, I have created two lists. The first list in column A contains dogs, cats, mice. The second list in column B contains 1, 2, 3. On sheet one, I have created the drop down list for the animals in the A12 cell. I would like for B12 to auto populate the corresponding number to the animal without having to create an if formula for each animal and number.
I'm in search of a VBA macro which will send a mail through outlook automatically with the count of files(.pdf + .msg files) in any given folder.
I work for an organization, where we use Outlook for mailing purpose.
Everyday Inventory(.pdf files and .msg files) will be saved in a folder that is to be processed.
I want a macro which should automatically send the total inventory (.pdf files + .msg files) through my outlook mailbox(say: me@outlook.com) to a my superiors outlook mailbox(say: superior@outlook.com).
If the macro sends that email to my superior before me and my superior logon to computers at perticular time (say: 7:00AM daily), need to plan our day at work.
I'd like to send data from an excel sheet to an XML file that already exists. Is there a simple way to use XPath to point to a tag (in which I would want to insert text) using VBA?
I am trying to create a script for a manually morning process that I need to complete that entials downloading a number of .PDF files and sending them out to a distribution list. I am unable to automate that actual saving of the files onto the network drive, but I believe I can automate the process of attaching them to the email. I have run across some problems so far though, this is what I have been able to come up with. After doing a bit of research it seems as though using cmd would be the best way to locate and attach the file but I am still unable to get it to run properly:
Code: Sub AttachMultiple() With CreateObject("Outlook.Application").CreateItem(0) .To = "test@gmail.com" .Subject = "Test" .Attachments.Add Split(CreateObject("wscript.shell").exec("cmd /c Dir 'G:Client ReportingMGIDaily ReportsAGFAJune 2013*.pdf' /b /o-d").stdout.readall, vbCrLf)(0) .Send End With End Sub
Here is an example of a folder I will be trying to pull the most recent saved PDF from, there are about 10 similar folders:
I feel like I am relatively close to completely this, my cmd syntax may just be a little off.
first post, i'll try to make this short and to the point. i have a file that has single invoice numbers, invoice dates and invoice totals. the line does not have a vendor name. the vendor name is above the invoice lines. (see spreadsheet).
Example: D2 = Vendor Lines 3-10 are individual invoices
Basically, I need to setup a formula that says if a cell in Column D is blank, then select the closest cell above the blank cell. I have 65000 lines with thousands of vendors. In the end, each invoice line will now have the vendor name that it is associated with.
I need to create unique IDs for all of my stock. I would like to use the concatenate function so that the first letter of each products type is taken and then joined onto a unique number. Im not sure how I will do this I was thinking that the formula looks at the largest value and adds 1 so that there are no duplicate numbers added to the first letter of the product type. E.g. the first mother board added will be "M1" the next will be "M2" as the formula has found that M1 exists and has added 1.
I would like to create a UDF that incorporates the Indirect Function. So if this is possible, I'd like to put in any cell..... =Tev(A2,D6)...... "Tev" would replace the Indirect function in the UDF script. In cell A2 would be the name of the sheet that I want to pull from. And Cell D6 would be the cell from the other sheet that I'd like returned.
I'm currently trying to put together a search function of sort onto an excel document to look for entries of "Y" based on the choices in a drop down list to populate a list of names that have a "Y" next to them. I've searched the net and read up on IF, VLOOKUP and HLOOKUP commands but I can't quite string something together. An example of what I am trying to achieve is below;
Where I have X number of Lists, each composed of two columns. The 1st column has the name of each item in each list, and the 2nd column has the value for that item.
What I'd like to do is create a function that would let me choose two lists, and tell me the amount of items in List X and what their values are in List Y, and then total them. Also, I would like it to work the opposite way, and tell me the amount of items in List Y and what their values are in List X, and then total them.
So for example, If I wanted to look at lists 1 and 2, the function would calculate that for List 1, Items D & E are found in List 2 and have a total value of 25%. For List 2, Items D&E are found in List 1 and have a total value of 17%. For all items not in both lists, it would return values of 0.
The code I came up with so far looks like this:
Function AK_Overlap_Go(x, y) Dim Temp(1 To 2, 2 To 1) Dim x As Integer
[Code]...
One of the problems I'm having is that the lists contain a different # of items, and so I think I need to loop the vlookup for each row. However, I'm not sure how to do that and get the cumulative values for each list.
I am trying to create a spreadsheet that uses a selection criteria based on different sheets within a workbook to output a costing quote, anything i can use as a template? I will post the sheet that i am working on that I need to combine into a working book.
I am trying to create a function that will evaluate a cell, lets call it B10, and depending on this numeric entry input data in cell ranges B14,B22.
What I am trying to do is create a list of locations for a packing slip that when I enter the corresponding location number (i.e. 200) that it will populate the shipping address in B14,B22.
I have been searching to see if it's possible to create a histogram with non-numerical data and it doesn't seem like it's possible. Is there something similar where I can. Count number of occurrences of a string and organize them from most frequent to less frequent? There are about 60 possible strings.
e.g. ABCDEFGHIJ 1ddhykygghd 2jlqqwertya 3ukhghjklzx 4tukyuuiioa 5xypyjkkadh
Then output the counts of each of the strings (letters in this case) present. I was thinking of just putting them in the same row or column and manually counting, but it doesn't seem like it would be that difficult have it automated.
Is there an equivalent of =rank for non-numerical data? If not, is it possible to sort data (into another column if necessary) alphabetically without resorting to a macro?
produce graphs/charts based on inequalities monitoring information - gender, nationality, age etc. I'm capturing info from several events, one sheet per event. Had a look at COUNTIF but not sure it's what I want and looks like I'd need sub-sheet for each? Capture.PNG
I'm working on a spreadsheet and I want the letter "E" to have a numerical value of zero, and not necessarily in just place as my sheet will be regularly updated. If you're a fan of golf, you will understand what I am doing. I'm basically making a custom leader board for a fantasy golf game that I'm doing, and I'd like for the letter E to represent the value of zero when I am adding up the scores.
For example A1: -1 A2: -3 A3: 4 A4: E A5: -2 A6: 3 A7: 3 A8: -5 Total: -11 (have a formula set up to only count the best 4 scores).
I want to be able to add up those columns, and since only the lowest 4 scores count, I need E to equal zero. I get that I could just put 0 there, so I'm being a little picky (in golf E represents Even par, essentially a value of zero for the purposes of this game). There are 20+ teams in the game so I need E to be recognized as zero in each of those fields.
I have a column of dates (2007, 2006, 2005 etc...) and a column of data ranging from "unsatisfied" to "very satisfied". I want a formula to provide, for example, the total number of occurences of "very satisfied" in "2006".
This should be easy but after searching "consolidate" all that I could find was multiple sheets and complex questions.
I have 5 data columns that I need consolidated into 1 single column. I've tried the consolidate funtion but it appears to only work with formulas and numerical values (unless I cant find the proper command.) I've also tried paste special but it populates the cells following.
I need 5 cells word values pasted into a single cell
I have Cell B1 = 6 on a worksheet named DOC. On Cell B3 of worksheet Week 1 it has "Celina". Is there a way that if Cell B1 in DOC has number 6, Cell B3 on Week 1 will automatically become Celina. All worksheets are in the same workbook.Likewise if Cell B1 ever has any of the following numbers, cell B3 in Week 1 will display the corresponding alphanumeric.
I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!
Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?
