Calculate IRR In VBA Macro Code
Jul 7, 2009
I used the following statements to (try to) calculate an IRR in my VBA program and received a runtime application error 1004. Tried several variants of the statement but cannot seem to escape the error.
Dim IRRValues(12)
For iIRR = 65 To 74
For iTIGRcubYear = 1 To 11
IRRValues(iTIGRcubYear) = Worksheets("TIGRCub"). Cells(iIRR, 3 + iTIGRcubYear)
Next iTIGRcubYear
aIRR = Application.WorksheetFunction.IRR(IRRValues, 0.2)
Worksheets("TIGRCub").Cells(iIRR, 14 + Scenario) = aIRR
Next iIRR
View 4 Replies
ADVERTISEMENT
Aug 22, 2012
I am looking for something to calculate values based on the past three months. I already have written a macro to retrieve all of the data and just need this as an add-on.
for example, lets say it is 5/31/2012. I have a row with dates going like
10/31/2010 11/31/2010 .........
I want the formula to calculate SUM(march+april+may row 68)/SUM(march+april+may row 59)
I have an input tab where I can select the month, say 5, and get the periods.
3/31/2012, 4/31/2012, 5/31/2012
if I changed the 5 to an 8 it would get 6/31/2012 7/31/2012 8/31/2012
I don't know if i need VBA for this but I am trying to have something to calculate this depending on the period selected in the input tab.
I am using excel 2010. I am thinking something with an offset function and maybe a loop? but still not sure.
View 2 Replies
View Related
Nov 25, 2013
In the first tab of my workbook I have a data table. In K2 down of that tab is a list of names, in D2 is their form, in I2 is the Type (there are 3 Types which start with (R), (P) or (S), in J2 is the points score associated with the Type and in H2 is the date they received the points.
For each name in the table I need it to sum the running points totals for (R), (P) & (S) Types and when they reach the minimum points thresholds below I want it to record the earliest DATE this threshold was achieved.
School Award: (R) = 20, (P) = 40, (S) = 20
Bronze: (R) = 40, (P)=80, (S)= 40
Silver: (R)= 60, (P)=120, (S)= 60
Gold: (R)= 80, (P)=160, (S) = 80
The output should look like:
Name
Form
School Award
Bronze
Silver
Gold
Anders Peter
7BN
20/9/13
15/11/13
Ball Jim
7BN
20/11/13
Carter Mark
7BN
19/9/13
8/11/13
24/11/13
View 6 Replies
View Related
Nov 6, 2013
How to calculate weekend (ONLY SUNDAY) in VBA.
View 3 Replies
View Related
Nov 27, 2013
I have a form which captures below details.
Label 1 = Time when form initialized or opened
Label 2 = Running Time (Live Time)
Label 3 = Total Time (Label 2 - Label 1)
Now what I need is since Label 2 is live time which shows the running time, Label 3 displays the total time spent. I want to display total time as running time.
e.g.
Label 1 = 13:01:00
Label 2 = 13:05:10 (running time/live time) which will keep ticking
Label 3 = 00:04:10 (I want even this as running time which keep running when label 2 is running)
View 1 Replies
View Related
Aug 10, 2009
I have a simple script that puts the save time in the last row of column C when the spreadsheet is saved. Another script puts a username in Column A and column B contains the time opened. I would like to add to the save script to make column D od the active row have a calculation similar to:
View 2 Replies
View Related
Dec 11, 2006
I have found this code while searching. I would like to know how to call the vba code only when clicking the button. On much bigger projects Excel has to re-calculate all cells everytime i change a cell value.
View 3 Replies
View Related
Dec 17, 2008
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.
Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"
I need a macro to "execute this code", as if it were in the macro itself.
I have assigned the above to a variable, but am not sure how to execute it.
EG.
Dim the_calc
the_calc = Range("B5").value
Now, how do I run the_calc ?
View 9 Replies
View Related
Sep 9, 2007
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.
1) Macro Recorder Code to Copy One Sheet to Another
Sheets("Data Apple").Select
Cells.Select
Selection.Copy
Sheets("Banana").Select
Cells.Select
ActiveSheet.Paste
2) Macro Recorder Code for Replacement Purposes......................
View 9 Replies
View Related
Jul 21, 2014
excel macro and i would like to ask u if i want to calculate a median of column of data and then the result i need to put in a new column with row by row....i need how to write the coding?
View 3 Replies
View Related
Feb 22, 2008
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)
The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...
The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).
The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.
'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23
'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub
View 3 Replies
View Related
Jun 26, 2009
I'm trying to do a forecast of my organization's budget for the next five years and need to create scenarios where certain figures will either be increased or decreased by a specified percentage that varies from 5% to 20%. I have recorded eight macros that can accomplish this task, but I feel like there must be a more effienct method.
In addition, I would like to increase or decrease the figures from either column H or column J. Column R is the results column. This is the macro I recorded that provides for a 10% decrease:
View 5 Replies
View Related
Sep 8, 2009
I have question on how to calculate the certain cells between 2 different worksheets based on the certain calculation parameters and the comparison result (the report) is put into the new worksheet on the same workbooks.
1) Worksheet1 contains: DEPTH, G1, C1, N1, D1, S1, SR1, MR1, DR1
2) Worksheet2 contains: DEPTH, G2, C2, N2, D2, S2, SR2, MR2, DR2
Expected result should be put in Worksheet3: DEPTH, G1, G2, calculation(G1 and G2), CI1, C2, calculation(C1 and C2), so on until the last columns. (find example on attached file "Test_Data.xls)
What I have done is to create a template to be filled in by copy and paste DEPTH, G1, G2 on worksheet1, DEPTH, C1, C2 on worksheet2, so on (attached file "Template_QC.xls"). Then I create a calculation macro for each of those worksheets separately to calculate the difference and color them . The calculation macro that I used is as follows:
View 5 Replies
View Related
Jan 25, 2012
Code to have a button perform two macros?
I need the button to
1. Calculate (perform F9 manual calculation)
2. Display a msgbox (MsgBox "Enter a city or zip code to calculate mileage.", vbExclamation, "Travel Schedule")
Is there an operator that lets you string multiple macros or do they need to be recorded separately and recalled in one macro assigned to the button?
View 1 Replies
View Related
Jul 8, 2002
I have a seris/column of numerical data for which I want to work out percentage differences and then calculate the standard deviation of the percentage differences for all values
For example in column A, I have
row 1 10
row 2 15
row 3 17
row 4 10
I want to work out % difference between row 1 and row 2 (which is 50%), row 2 and row 3 etc and the get the standard deviation of thoose % changes.
I know how to do this on the spreadsheet using formulas but I want to do it using a macro so that the macro returns the standard deviation of the percentage changes because I do not want to have any data showing on the spreadsheet other than the raw data.
kkknie kindly provided the following UDF to work out the the average of the percentage changes but is there code that will work out the standard deviation instead?
I thought maybe excel's built-in standard deviation function (stdev)could be incorporated somewhere into the code but I'm not sure how!
Function AvgPct(rngIn As Range)
Dim dblErrAccum As Double
Dim intErrCount As Long
Dim dblLastVal As Double
Dim r as range
dblErrAccum = 0
dblLastVal = 0
intErrCount = 0
For Each r In rngIn
intErrCount = intErrCount + 1
If intErrCount > 1 Then
'dblErrAccum = dblErrAccum + (r.Value - dblLastVal) / dblLastVal
dblErrAccum = dblErrAccum + Abs((r.Value - dblLastVal)) / dblLastVal
End If
dblLastVal = r.Value
Next
AvgPct = dblErrAccum / (intErrCount -1)
End Function
View 9 Replies
View Related
Jun 5, 2008
I have just been passed a new VBA project and there is one component I would like help with.
Column J lists a start date
Column K lists an end date
Column N lists a dollar amount.
Columns P through the last column has headers of month-year in text form(for example May-08)
The user enters contract information in the spreadsheet including the above cells. What they want to automate is once the user enters the contract information on a given row, she runs a macro to determine the billing schedule by calculating how many months there are between the start and end dates, divide the dollar amount in column N by that number and then prompt the user to enter what month the first payment is due, once that is entered, the macro should place the payment amount in that column and all applicable subsequent columns.
For example,
Column J = 6/1/2008
Column K = 9/1/2008
Column N = 3,000
The macro should calculate 3 months, the monthly payment amount is $1000.
A starting month prompt appears and the user enters July-08. The macro then places 1,000 in the columns for July-08, August-08 and September-08.
View 9 Replies
View Related
Nov 28, 2009
I have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
View 9 Replies
View Related
Feb 1, 2007
I need to develop a work sheet for agency booked people to count the number of hours worked by them on daily basis. Agency can clock in at three different times and clock out at 6 different times. i tried but could not even develop logic to calculate the total earned hours. i attached the sheet for reference.
View 5 Replies
View Related
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jul 21, 2007
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
View 2 Replies
View Related
Feb 12, 2014
So I'm trying to create a macro that will simplify dealing with a file we receive at work pretty often. I've enclosed a spreadsheet that shows a very basic example of the files we receive. What I need to happen is this: On the rows that have only the Company # in column A and Total $ in column D (ie. rows 5 and 6), I need the Total $ amount moved up one row and then for the row it was previously in to be deleted (at that point that row should only have the Company $ and no other data in it so it's basically useless). The problem I'm having is that if any rows are added to the file (for instance if an row was added between rows four and five) it would throw off my macro.
View 3 Replies
View Related
Feb 17, 2014
The following macro does well at concatenating 2 constant values and the values in columns A and CF and places it in the CP field. What I need to know is is it possible to modify this code so if there is NOT a CF field cell value (that is it is blank) that the formula does not run and moves on to the next row? That is, the CP1 cell remains blank?
View 1 Replies
View Related
May 8, 2008
I would like a macro which works on the selected cells:
A number of the selected cells contain formulae with an apostrophe in the beginning.
I need the macro to remove the apostrophe and calculate the formula in those cells..
View 9 Replies
View Related
Aug 10, 2014
Refer to attached file.
I have below code which successfully create a macro button and assign the macro correctly.
This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.
[Code] ....
Test Macro_Botton.xlsm
View 3 Replies
View Related
Feb 7, 2008
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.
My worksheet runs a lot of code, then deletes all macro code and saves itself.
The problem is the next time i open I still get the macro security warning!
I've checked thoroughly and there is definatley no vba left anywhere.
If I open the document, enable macros, and save it, then open it again, I no longer get the warning.
View 9 Replies
View Related
Apr 2, 2014
I am trying to come up with a way, and do not know if it is possible. In the attached spreadsheet, I am looking to find a way that will automatically filter a group of records from the f column (all CC, all 2ndR, all NA) then calculate the information in in Columns G and H and place it in the appropriate box in D-21 through D 37. Example. I want all the rows that ONLY have CC in Column F. Then take the information from those records in Column G and column H. I want the sum of Column G in D21/2 and Column H in D23/4. I
View 2 Replies
View Related
May 2, 2012
I have a column of data where the seperate entities are divided by blank rows. for example: i need to calculate sum(G3:G15) then skip the blank row (G16) and calculate the next group, say sum(G17:G50) and export that import to a new worksheet.
The issue is that the column lengths vary from day to day so the blank rows will not be in the same position.
View 2 Replies
View Related
Nov 20, 2008
Is there a way to use goal seek in VBA that won't trigger a full model calculation?
I would like to (somehow) perform goal seek, but only recalculate range("A1:B50") say on a single worksheet (where both the input value and the final calculated value is included in that range), as opposed to calculating the whole model inbetween each iteration.
Also, all dependencies between the input value and the final calculated result are contained in that range.
View 5 Replies
View Related
Aug 7, 2008
I am trying to create a spreadsheet to calculate the macro nutrient ratio of foods based on carbohydrates, proteins, and fats and how many units/servings I eat of each. I've attached the file I've started. It's fairly straightforward and simple, thus far.
I would like to be able to associate a food with its various nutritional data in its row (fat, carbs, sugar, fiber, sat. fats, protein.. etc.). With all of my food items entered, I would like to be able to choose from a list, have it prompt me for how many units I've eaten then plug it into my formula to calculate totals and ratios.. The final flow should go soemthing like this...
1. Select a food from a list (each food on the list has its associated characteristics)..
2. Select how much ( each food will have an associated serving size i.e. 1/2 cup, 1oz..) ---- (4) oz VS. entering each unit in A1
3. Make these totals of each characteristic feed into my final chart with which to track and graph.
View 4 Replies
View Related
Nov 16, 2009
I have a small data set (ends up with 8 columns, up to 1000 rows), that gets imported sorted and formated via a macro linked to a button. I need to be able to look at one particular column of info, which will contain different text values every time the data is updated, and create a list of all the different values that occur in that column (maybe 10 max). Then...... I need to calculate a numerical value from adjascent column, linked to the text values from the first process. Below is an example to better explain:............
View 2 Replies
View Related
