Custom Function With Variable Number Of Input Parameters?
May 10, 2013
how to create a custom function with an unknown/changing number of input parameters?
Specifically, I need to create a function that will calculate the average of 1,2 or more ranges. I can't use the built-in average function as I then need to use the average in my function.
what I have so far is:
function points(current as range, previous as range)
if previous > current then final=1
if previous < current then final=0
if previous = current then final=2
points=final
end function
This works okay if I only only have 1 range of values. but in my worksheet I need to be able to enter =points(a1,c1:f1,g1) and have the function calculate the average of c1:f1,g1 and compare it to a1.
View 6 Replies
ADVERTISEMENT
Apr 22, 2009
Does anyone know why you can't specify an array, like an array of Doubles, as an argument or input to a custom function? For example:
View 14 Replies
View Related
Oct 18, 2006
A custom function as shown below is not reading in the value of the parameter Ttorefinance which is an Excel cell value (actually a link in the spreadsheet). Eg when Ttorefinance cell value is 13, the function reads in 0.
Function MarginbyTranche(TfromIssue As Double, TtoRefinance As Double, AssetType As String, Tranche As String) As Double
Dim RmbsAaaMargin As Double
Dim RmbsAa3Margin As Double
Dim RmbsBaa2Margin As Double
Dim CmbsAaaMargin As Double
Dim CmbsAa3Margin As Double
Dim CmbsBaa2Margin As Double
Dim PsAaaMargin As Double
Dim PsAa3Margin As Double
Dim PsBaa2Margin As Double
View 3 Replies
View Related
Jun 3, 2014
I have declared a 'long' variable. however; how do I change the variable data type to string if user input is not a number? how do I recognize the user input's datatype?
View 7 Replies
View Related
Mar 2, 2012
I have a pivot table that has several row labels and 1 column label. I am trying to create a Ranking table outside of the pivot table because I am using Excel 2007 and can't use the new Rank option in Excel 2010. My difficulty is in being able to rank each subgroup of the row labels.
I have thrown a column for "Target Rank" in the data, just to validate my calculation in the PivotTable worksheet, but it is only correct as long as all Company and Product Name options are selected for a given Sex, Age, and Scen Defn.
My non-dynamic formula is:
=RANK(GETPIVOTDATA("Target",$G$5,"Company","AIG a","Product Name","Elite Index UL a","Sex","F","Age",25,"Class","1-Best NS","Scen Defn","Overfunded A"),$L$8:$L$30).
I have figured out how to make some of the items variable to this degree:
=RANK(GETPIVOTDATA("Target",$G$5,"Company",$I8,"Product Name",$J8,"Sex","F","Age",25,"Class","1-BestNS","Scen Defn",$K8),$L$8:$L$30).
I know can make the $L$8:$L$30 copyable across columns by changing it to L$8:L$30.
My dilemma is several fold.
1. If I filter the Company or Product Name the $30 in the range no longer applies. Is there a way to make the bottom of each grouping dynamic?
2. Is there a way to make the item entry associated with the "Class" column label a variable so I can copy across columns?
3. Similar questions related to the row labels for Sex and Age?
4. Lastly, I can write an expression that would make my Rank columns blank if the Company column was blank, but is there a way to reset the data range to look at the next block? That is, if "Age" changed (was not blank) that would indicate the top of a block of data.
I have tried to think of ways to use INDIRECT and I don't know VBA well enough to do it in a macro.
ClassValues1 - Best NS4-Nonsmoker1-Best NS4-NonsmokerTarget RankAV 5 RankAV 10 RankTarget RankAV 5 RankAV 10 RankSexAgeCompanyProduct NameScen DefnTarget Target RankAV 5AV 10Target206420125F25AIG aElite Index UL aOverfunded A 1,520 20 12,848 33,607 2,135 9181961618Allianz LPLife Pro+Overfunded A 2,796 9 10,613 26,147 2,847 7221892217American GeneralElite Global PlusOverfunded A 2,810 7 7,930 26,323 2,810 1913617198AmericanGeneralChoice Index GULOverfunded A 1,610 19 11,388 31,409 2,205
[Code]...
View 1 Replies
View Related
Jun 23, 2014
As you can see in the attached excel doc, I have calculated a column for "Number of Claims Per week" and also "Dollar amount Per claim". This was done using VLOOKUP and distributions. That is not the issue.
The issue I am having is creating the "TOTAL" for the week. So for example, if week one generates 4 claims, I need to be able to add up claim amount #1-#4.
It seems as though this would be a basic function, but I can seem to find a way to add up a certain number of cells, based on a variable generated from a random number.
If you take a look a my excel sheet, I am trying to solve for the "Claim Total" column on the far right. This column should be the sum of (Number of claims * The individual amount per claim). Note that if there is 4 claims in that week, I would have to add up claim 1-4 to get that weeks total.
Question2.xlsx
View 9 Replies
View Related
Apr 26, 2007
what is the function of the (1) within the below trim function.
The value of A2 is "ABC - 123 - Lovely"
Range("A1").Value = Trim( Split(Range("A2").Value, "-")(1))
View 9 Replies
View Related
Jun 28, 2014
I have this code snippet..
Code:
Function appointment(ByVal day As String, ByVal hour As Integer) As String
' Insert code to return any appointment for the given day and time.
Return "appointment"
End Function
But I get this error. Compile error: Expected: end of statement
View 2 Replies
View Related
Mar 23, 2009
I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.
I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.
View 3 Replies
View Related
Oct 17, 2009
The code below works fine, I can copy selected area into new sheet. However, I wish to custom change the Range("A1:AO164") value, for example, at cells(1,1) I key in A1, then cells(1.2) I key in A56, then this changes will reflect to the value in this macro to Range("A1:A56"). I tried using this way :
Dim A as string
cells(1,1).text = a
cells(1,2).text = b
Range("a:b").Select
View 2 Replies
View Related
May 11, 2014
i am looking for excel custom number format for 0 (zero) number that make center alignment..
for example ;
sample (when type 0 (zero) number)
after custom number format
- (right alignment)
- (center alignment)
how make center alignment with custom number format for 0 (zero) number..
View 4 Replies
View Related
Dec 5, 2007
I want to create an input box that will allow you to input the column number it should start in... so instead of it being fixed at A, you can choose column B, C, D all the way to the end if you like.
Sub SplitSheetsNoDelete1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lmainsheet As String
Dim Lrow As Integer
Dim lcontinue As Boolean
Dim LColamaster As String
Dim lcolatest As String
'Retrieve name of sheet that contains the data
Lmainsheet = ActiveSheet.Name
'Initialize variables
lcontinue = True
Lrow = 2.....................................
View 9 Replies
View Related
Nov 17, 2009
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
View 4 Replies
View Related
Aug 8, 2009
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
View 4 Replies
View Related
Apr 10, 2013
i know i have done this before but im totally blanking. I want to user the inputvalue in procedures called from controller, but it just shows as empty.
Code:
Sub controler()
Static inputvalue As Long
inputvalue = InputBox("Please set min size of trades.")
'other macro's called here
end sub
View 4 Replies
View Related
Oct 12, 2009
Example:
Order Number from VendorOrder Number from SupplierTracking Number from Supplier12345123451ZABCD13456134561ZBCDE134561ZCDEF14567145671ZDEFG1456714567
In these three orders, the first order nubmer has a 1-to-1 relationship with a single tracking number. In the second, there are two different tracking numbers associated with one number from column A. In the third, there is only one tracking number but three different lines of the same order number that need to be updated with it.
The question is, is there a way to line up data fields head to head even when there are variable inputs like this?
View 15 Replies
View Related
Jul 17, 2007
I have a Workbook containing a list of records. I'd like to create some code to
1) Get the user to input their initials when the workbook opens, and capture the response
2) Clear any filters currently applied
3) Apply filters to a non-continous range of columns
4) Apply the input response as the criteria to one of the columns, and 'blanks' to another 2 columns
Private Sub Workbook_Open()
Static varUser As String
'sets varUser variable as String & stores it
varUser = InputBox(Prompt:="Enter Intials (leave blank to view all)", Title:="Login")
'Activates input box to capture value
Dim ContractsSheet As Range
Set ContractsSheet = Worksheets("Contracts").Range("A1:AA999")
ContractsSheet.EntireRow.Hidden = False....................
View 4 Replies
View Related
Nov 25, 2009
I have a custom data entry form which is working fine. The form completes customer data for reviewing at a later date. I have now been asked to change it so it will seperate the data in to customer's who require some documents sent out and some that dont.
Is it possible to have a combobox on the form and if the options on the combobox are Yes and No (original), when either option is selected the data will complete on to a seperate sheet for cases with documents required or documents not required?
View 4 Replies
View Related
Aug 11, 2004
I am wanting to create a custom function that i can enter into a cell to run a macro (MyMacro). I do not know how to write a function, but so far i have:
Public Function Run(MacroName As String)
Application.Run MyMacro
End Function
View 6 Replies
View Related
Dec 11, 2009
Custom average function. can this be done with Worksheet functions:
View 4 Replies
View Related
Jan 28, 2009
This just a shot in the dark, but does anyone have a custom function that calculates the check digit for a cusip? http://en.wikipedia.org/wiki/CUSIP
The algorithm is listed above.
View 3 Replies
View Related
Jun 14, 2009
Imagine i have 2 columns: Open and Close, both of these got numbers like
1 ---- 2
3 ---- 1
4 ---- 10
and so on. I had to make a function which checks if some number is in between any of those Open and Close numbers and count how many, for example: im searching for number 1.3, so according to previously drawn table i would get answer of 2, because 2 is in between 1----2 and 3-----1, i achieved this by a simple function:
=IF(OR(AND(Bendras!$J$1>=Table1[[#This Row],[Open]],(Table1[[#This Row],[Close]]>=Bendras!$J$1)),AND((Bendras!$J$1<=Table1[[#This Row],[Open]]),(Table1[[#This Row],[Close]]<=Bendras!$J$1))),TRUE,FALSE)
this generated an additional column with TRUE and FALSE values which i counted with:
=COUNTIF(Table1[T/F],TRUE)
and got the answer.
so now then preparations are ready i need to make a function which would for example if the number i was searching was in 10th and 45th rows find the MAX/MIN values of Close column between those rows(hope i made my self clear)
this is how i was hoping to do that : first of all make a new array of all cell addresses from "Close" column which were "TRUE" from the first function i wrote and when do w/e i like with those cell addresses in other functions.
View 12 Replies
View Related
Nov 21, 2011
My co-worker created a function that would calculate the standard deviation according to whatever range is selected by the user.
I was hoping to add an if statement to the formula but am getting a #value! error.
coworker's function:
Function volatility(r As Range, Optional scl As Double = 252) As Double
Dim lr() As Double
rws = r.Rows.Count
ReDim lr(1 To (rws - 1)) As Double
Dim variance As Double
[code]....
What I'd like to do with the function:
=volatility(IF((YEAR(A8:A1036)>=K3)*(YEAR(A8:A1036)
View 3 Replies
View Related
Sep 9, 2009
I have created a custom Function in excel. I would like to create a custom button so I can run this funtion with a Click of a button unstead of typing it in or clicking on the insert function button. I am not sure if this is possible to have VB call a custom function. Below is my VB for the Custom Function. I basically want the custom button to open the formula in excel.
Public Function CreateFlexstring(Company As String, Cost_Center As String, _
Division As String, Geography As String)
CreateFlexstring = Company & "-" & Cost_Center & "-" & Division & "-" & Geography
End Function
View 9 Replies
View Related
Jul 10, 2009
I have a column of data with 2 possible values, "H" or "A". The second column contains numbers (unsorted).
How do I write the VBA code to return the Highest number, where the first column is 'H' ?
Eg.
Location Attendance
H 25365
A 17436
A 47252
H 15494
A 37578
H 17549
H 28756
A 59756
This would need to return 28756.
View 5 Replies
View Related
Feb 27, 2007
I am very proficient at Excel/VBA and have a question about custom/user-defined functions that may be a little more advanced. I understand how to write custom functions and access them through the user-defined functions menu, but I would like to be able to include my function in an add-in that users could simply add, and then access the function via the 'Fx' box at the top of Excel, like they would any other built-in function, instead of having to go to the user-defined functions menu. I guess what I am looking for is how to add 'built-in' functions and not user-defined ones. I just want the user to start typing '=customFunction(' and have the parameters pop-up in tool-tip form, like any Excel built-in function would.
View 9 Replies
View Related
Jun 6, 2014
I have create an excel formula that I need to input data daily.
Every day, I need to insert a line and input my new data.
Below is my formula
=IF(V159="Skip","Go",IF(P160>0,IF(COUNTIF(C60:D154,">"&H159+3),"Good"),IF(COUNTIF(C60:D154,"<"&H159-3),"Good")))
The problem is that when I insert a line. Most of my variable will change according except C60:D154
As you can see below
V159 change to V160 = This is good
C60:D154 do not change accordingly. How can I make it change accordingly ?? Why it is not changing when I insert a line ?
=IF(V160="Skip","Go",IF(P161>0,IF(COUNTIF(C60:D154,">"&H160+3),"Good"),IF(COUNTIF(C60:D154,"<"&H160-3),"Good")))
View 7 Replies
View Related
Mar 21, 2014
I have a spreadsheet where the start date is manually entered in Cell B1 and an end date is calculated in Cell B3. I would like to have the dates between the start and end date, with a step value of 7 days show in the rows under Cell B3. How do I get this to work?
View 3 Replies
View Related
Jul 23, 2009
I need an action to allow for the random selection of a predetermined series of number codes which represent a given letter.
It is probably easier to look at the attached sheet and the previous discussion to see what I mean.
Previous discussion:
http://www.excelforum.com/excel-work...-of-cells.html
View 2 Replies
View Related
Nov 19, 2008
I have a calendar I have developed that highlights the current date and opens by default to the current month. Each worksheet is a seperate month. The current date highlighting is written with a public macro and the opening to current month is a private macro. The problem comes when this workbook is shared and the other users have to agree to accept my macro because of security. I would like to bypass that requirement and just have the workbook stand alone without the permission.
View 5 Replies
View Related
