Function Argument's Cell Adress
Sep 16, 2009
I have an attached file and I am trying to build a VBA function to calculate total values. First row is "RollingTime" and for example if I am trying to calculate the "Total" value. For "RollingTime" = 2 it should be
RollingTime(2)*Percentage(2)+RollingTime (1)*(1-Percentage(1))*Percentage(2)+RollingTime(0)*(1-Percentage(0)*(1-Percentage(1))*Percentage(2)
Which is 109732508*0,3 + 1017508995*(1-0,2)*0,3+1587172158*(1-0,1)*(1-0,2)*0,3
And here is the code I have tried to produce:
View 10 Replies
ADVERTISEMENT
Dec 3, 2012
I have the following problem: I have this formula, that works correctly:
=SOMMA.SE('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12;E1;'[myfile_12112012.xlsx]RIEPILOGO'!$B$2:$B$12)
I wish I could read the name of the file (myfile_12112012.xlsx) or, alternatively, all of the function's first argument (('[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12) from a separate cell.
I tried this:
SOMMA.SE = (C2, E1, '[myfile_12112012.xlsx] ERROR SUMMARY'! $ B $ 2: $ B $ 12)
putting in the cell C2 the string:
'[myfile_12112012.xlsx]RIEPILOGO'!$C$2:$C$12
In this way, however, the formula does not work.
How can I read and evaluate correctly the first argument from the cell c2?
View 2 Replies
View Related
Sep 25, 2008
how to create a function argument containing my own defined formula?
View 9 Replies
View Related
May 23, 2008
formula to calculate the commission based on 9 cases each case is subjected to 4 differant arguments.
here is the formula for the first case (the answer if true 1.4) & the last case( if all the casses are fails it should show 0.6) u will find it in cell K5
=IF(AND(E5>=20,H5=1,I5>=80,J5>=80),1.4,0.6)
this is only 1 case HOW can i calculate it basd on 9 casses??
View 10 Replies
View Related
Jan 23, 2008
When I try a MATCH function, I substituted the range through the ADDRESS function. But, it returns #VALUE error. When I manually typed the same range address, it produces the results. I've been behind this more than 8 hours still I can resist the heat on my cool head! But, I thought you coolest head guys need some heat to warm up for to-day's challenge.
View 9 Replies
View Related
Sep 12, 2006
In the code bellow I would like to automate a if function until the 1st emptycell in the row 7 of my spreadsheet.
In my IF function, I would like to refer to a fix cell ("B1") while the other argument is in the same column as the function but two rows bellow.
My problem is that I don't figure out how to refer to a fix cell with the syntax I would like to use t run the macro.
I think it's more clear when you'll see the code
Sub Face2face()
Range("B5").Activate
Do
If ActiveCell.FormulaR1C1="=If(R[2]C>=Range("B2"),Range("B2"),R[2]C)"
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell.Offset(-1, 0))
End Sub
View 8 Replies
View Related
Jun 25, 2013
When I step-through my code below, it always opens the first file in the directory "C:Pyramid Files", but when it comes back to the Pyramid Files sub after fully processing the first file via various other subs, the VB Editor apparently doesn't like something about this line: StrFile = Dir(), since it quits after "snapping-back" to the previous sub Initialize(). I have also tried StrFile = Dir, but that doesn't work either. I did Dim Strfile in the General Declarations. When I set Watches for Dir and Dir(), I get the value "Invalid procedure call or argument" for both, as if the directory function lost the value. I can't determine why this is happening.
VB:
Dim WSM As Worksheet, WSB As Worksheet, WS1 As Worksheet, [U]StrFile As String[/U], StrDirectory As String, ClientCode As String
Dim Filename As String, LastRowb As Long, LastColB As Integer, LastRow1 As Integer, NextRowC As Integer, x As Integer, y As Integer
[Code] .......
View 4 Replies
View Related
Feb 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 Replies
View Related
May 6, 2009
I have made my own excel function that my users needs every day. it works just fine, however if you do not know the required argument for this function, you couldn't use it. How can i prompt my users for the right order of the argument when they type the function in excel?. example is when you type the Function "=left(" you will be prompted by excel of the correct arguments.
View 3 Replies
View Related
Jun 27, 2006
I have a function in a cell triggered on the value returned by a DDE link in another cell.
e.g. cell A1 contains =function1(A2) and cell A2 contains
=ADVFN|NYSE_CAT!CUR
function1 is triggered each time the DDE link updates regardless of whether the result returned has changed or not. How do I prevent function1 being triggered if the returned value has not changed?
View 9 Replies
View Related
Aug 30, 2012
This error message in line vpp:
Invalid procedure call or argument
Code:
Function fn1(ByVal a, ByVal i, ByVal e, ByVal N, ByVal w, ByVal ta)
Pi = Application.WorksheetFunction.Pi
mhu = 398600
vpp = (mhu / Math.Sqr(mhu * a * (1 - e ^ 2))) * (-Math.Sin(ta * Pi / 180))
fn1 = 2 * vpp
End Function
View 9 Replies
View Related
Jul 2, 2013
When I attempt to send a spreadsheet using a German version of Excel, the following formula does NOT work properly.
=ZELLE("adresse",C14)
The function gets translated into =CELL("adresse", C14), and what I need is, obviously, = CELL("address",C14). The argument does not get translated.
View 1 Replies
View Related
Dec 28, 2013
Can we use an array like {1;3;4;5} as Row or Column argument in INDEX function? Like shown below
=INDEX($A$1:$E$10,0,{1;3;2;4;5}) OR =INDEX($A$1:$E$5,{1;2;4;5;3},0)
View 9 Replies
View Related
May 29, 2007
In VBA, True takes the integer value of -1. However, in Excel, it is 1.
I am writing a custom function that does arithmetic on a value. However, due to above, if the value is True, my custom function produces a different result than if I wrote an Excel formula to do the same thing.
Is there some way to identify the data type of a value passed into a function? Currently my function looks like this:
' Return the valueToIncrement with its magnitude increased by PctToAdd
Public Function INCREMENTPCT(valueToIncrement As Variant, PctToAdd As Variant) As Variant
INCREMENTPCT = valueToIncrement * (1 + PctToAdd)
End Function
Ideally I would like to identify whether the type is Boolean and adjust the value so as to give the same result as an Excel formula would give.
View 8 Replies
View Related
Sep 8, 2005
I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.
To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.
If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.
Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer
Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i
Mean = Sum / UBound(Arr)
End Function
View 14 Replies
View Related
Jul 31, 2014
I have some code I wrote that copies sheets from another sheet in another workbook to my current one, in order. However, depending on activity within these sheets, there might not be a sheet for the month.
I have the on error code to go to the next argument, however I would like it to tell me on my errors sheet that it did not exist.
The error sheet will simply have 2 columns of copied sheets and non-existent sheet.
VB:
On Error Resume Next
Workbooks("GLTemp").Sheets("981715").Copy After:=Workbooks("MHSGMR").Sheets("981715 Budget")
Workbooks("MHSGMR").Sheets("981715").Activate
sheetName = ActiveSheet.Name
Workbooks("MHSGMR").Sheets("981715").Select
ActiveSheet.Name = sheetName & " GL"
[Code] ......
View 3 Replies
View Related
Oct 20, 2009
I have the following formula which works fine in this form:
View 4 Replies
View Related
Dec 19, 2013
I am coding a spreadsheet that makes extensive use of the excel dropdown list boxes. So I have codes such all over and it is not a neat way to code.
Code:
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
As many of the dropdown list boxes are similar in nature, with the only exception that the list content is different, I wanted to code a sub routine to include the code above.
My subroutine looks like this now:
Code:
Sub listbox(cellref As Range)
Set Target = Cells(cellref)
With Target.Validation
.Delete
.Add xlValidateList, 1, 1, Formula1:="1, 2, 3"
.InCellDropdown = True
.ShowInput = True
End With
End Sub
The problem is that when I call the subroutine with a
Call listbox (10,10)
It kept giving me a compile error.
I would like to create a listbox at cell row 10, column 10 of the worksheet.
View 9 Replies
View Related
Nov 4, 2009
im having trouble with an IF argument as you can see from example the data sheet is where i need the result,the first formula i have done in B24 but cant understand why the 2nd argument will not go in and i keep getting a value error as shown in C24.
i need say If in the sunday tab G8="A1"then insert G32.Then do it for "A2"=G32 and so on.all in Data B24
Is there a shorter way as i have to go up to A9 B9 C9 D9.
View 2 Replies
View Related
Nov 11, 2009
I have a sheet that has 2 fields that i need a yes/no answer from. Basically if b = "P1" then a should be less than or equal to 4 to reply good otherwise it should reply no. but if b isnt "P1" but instead is P2 P3 or P4 then it should => 10, 30, 40 respectively.
View 8 Replies
View Related
Mar 15, 2009
does IF in excel formula take 27 Arg and ( AND, OR) in IF ststement take the same Arg
and does SumProduct Take the same Arg .
View 9 Replies
View Related
Jul 19, 2007
The second subroutine below selects a range of cells, in this case a column. It selects a range that ends with the last cell that has data and starts with either the first cell that has data or the second cell has data (needed for instance if the first cell is just the heading and doesn't contain data). You'll notice I have hardcoded the "includeFirst" option right now. I don't know how to pass this as an argument when calling this subroutine.
In the first subroutine updateAll() I need to call the second subroutine and want to pass an argument to it. Maybe my approach using subs and application.run is the wrong way to do this?
Sub updateAll()
includeHeader = False
Application.Run "SelectColumn"
' need something like:
' Application.Run "SelectColumn(includeHeader)"
' where subroutine below has something like:
' Sub SelectColumn(includeFirst)
End Sub
Sub SelectColumn()
'Select a Column or Row of Cells in a Used Range Quickly
'http://msdn2.microsoft.com/En-US/library/aa155432(office.10).aspx
includeFirst = False
'includeFirst = True
Dim UpBound As Range
Dim LowBound As Range
If ActiveCell.Row > 1 Then
If IsEmpty(ActiveCell.Offset(-1, 0)) Then
Set UpBound = ActiveCell........................
View 3 Replies
View Related
Jan 8, 2014
Is it possible to create an IF argument based on the filename?
Example - A2=IF(Filename="template",A1,Filename)
Reason - A1 is the result of a MAX function on the template WB but once template is saved as a different file name I want A2 to = the new filename?
View 10 Replies
View Related
Nov 3, 2009
I'm fairly new to VBA (exactly 8 hours of paid experience ) and I've stumbled upon the following.
Say I wanted to select a certain range of cells (which later have to be merged, don't ask why, they just have to. It's in the job description and it's not in my power to change things around here).
View 14 Replies
View Related
Dec 22, 2009
i have a calendar with the dates in a row
I want to count instances of an entry under the dates using 'countif' from 01JAN until today.
My problem is that today changes value [up by one] each day so the 'countif' function will need to extend its range to the right by one cell each day.
View 11 Replies
View Related
Nov 7, 2011
I am trying to write a macro that has multiple if arguments.
An example is as follows:
If cell b11 had fill colour red, then colour fill cell B8 green,
If cell b11 had fill colour green, then colour fill cell B8 red,
If cell b11 had fill colour yellow, then colour fill cell B8 blue,
If cell b11 had fill colour blue, then colour fill cell B8 yellow,
end if.
View 5 Replies
View Related
Sep 24, 2012
There are 100 loans, each one of them need to be tested on 11 different tests, the results are either "pass" or "fail". All the test results are stored in the Boolean array. g_sel_tag(). For instance, if g_sel_tag(2,7) = true that means the 2nd loan has failed on the 7th test.
But I got stock with the following code, I think there should be a loop for the public variable rep_num in order to loop through the 11 test results for each loan. I have searched the entire project but did not find any value was assigned to the variable rep_num. The following code is part of a huge project, I can't test run it to see if the following code is valid at this point.
I am new to VBA therefore wondering if it is valid with no specific value assigned to rep_num. (I think there should be something like "for rep_num = 1 to 11" in order to get it running).
There are several codes in the project with similar structure where they just use rep_num without assigning any specific values.
Code:
Dim rep_num As Interger
Sub test_results_reporter (rep_num As Interger)
Dim i As Interger
Dim g_sel_tag(1 to 100, 1 to 11) as Boolean
For i = 1 to 100
If g_sel_tag(i,rep_num) Then Msgbox "Loan" & i & "has failed on test" & rep_num
Next
End sub
View 2 Replies
View Related
Mar 14, 2013
In the Formula below I am attempting count records that are not "Closed" or "Cancelled"
But records with those two status values are still being counted I have a feeling that my attempt to create a "not equal to" condition may be the problem, even though Excel did not error when I put in this formula. I am certain that the worksheet and columns that these conditions are pointing to are correct. Note that the first 2 conditions are working correctly.
=COUNTIFS('QC Extract'!$C:$C,$B34,'QC Extract'!$I:$I,F5,'QC Extract'!$H:$H," Closed",'QC Extract'!$H:$H," Cancelled")
View 2 Replies
View Related
Jun 4, 2009
I need a simple IF AND formula. I just can never manage to get them right. I need a formula (in Column M) to evaluate a date count in Column L (which is a count of business days since another date, fyi). It needs to check to see if the number of days since the date in Column L is greater than 10 AND if the value of Column I is one or zero. If the argument returns true (more than ten days and a zero in Column I) it should return a vaue of "Yes" and "No" if false.
View 9 Replies
View Related
Dec 11, 2006
I want to be able to call a procedure and pas 2 variables that it will need to it.
The code looks like this:
Private Sub UpdateScreen(StartLine As Byte, EndLine As Byte)
End Sub
UpdateScreen(104 , 140)
For some odd reason I get an "Expected: = "error when I enter the last line. I works if I send only one variable, but not when I send 2.
View 2 Replies
View Related
