I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.
Function Multiple_Cell_Above()
Multiple_Cell_Above = cellabove * 10
End function
I am trying to use the vlookup function but the range of reference has that little green triangle in the corner and if I f2 each cell that triangle goes away and the vlookup function works great. Do you know what is going on here? And also, a quick way to convert all the cells with the green triangle to be without the triangle in a whole range? I don't want to have to go to each cell individually and f2. I've attached an example worksheet for reference to what I am describing. vlookup format.xlsx
View 2 Replies View RelatedBasically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
I'm trying to create a user-defined function, but the function needs to know what cell it has been placed in. I want to use this cell reference to get some other offset values. I can easily find out offset values based on the ActiveCell, but not for those offset values based on where the UDF is actually placed, which could be almost anywhere on the worksheet.
View 5 Replies View RelatedI understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
View 3 Replies View RelatedI saw there were codes to sum or count cells that have a specified Fill Color
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
However it says the changing of a cells fill color will not cause the Custom Function to recalculate, I really need to recalculate the data if there are any changes in the selected range
How would you write a custom function that you could plug in a cell that would do the following.
If the value in the cell is greater than 0, traverse up that column to clear the values until it reaches the cell in that column that has been colored orange.
I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
See the attached example for an illustration of my problem. Essentially, I am trying to reference the first two numbers of an account number when using VLOOKUP. The function then references a table with account classifications based upon the first two numbers of each 10 digit account. This seems so basic, but is driving me up the wall!
View 3 Replies View RelatedI have this MAXIF array function that I am trying to use but I need it to reference the Last Row and not the absolute refererence. I am a novice at VBA. The first max if array is the one with my effort to try and get the last row. It doesnt work. The second one works but references the cell.
Code:
Range("U2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:RC1 & LR=RC[-20],'Monthly Production'!R2C19:RC19 & LR))"
Range("V2").Select
Selection.FormulaArray = _
"=MAX(IF('Monthly Production'!R2C1:R8729C1=RC[-21],'Monthly Production'!R2C21:R8729C21))"
I'm building a table that references a dynamic range on a pivot table. So far I've gotten the function to return the address on the pivot table but it wont return the value. I'm doing this so that I can both refresh the two source data sets and summarize (pivot table) and also graph these two summarized data sets since you can't graph two different pivot tables together.
This is my code so far:
Iferror - To return zero if the source data doesn't exist yet
Address - To concatenate the match functions
Match - to find the row and column numbers on sheet "B" that match the x,y call on the original sheet "A"
=IFERROR((ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
Returns $B$5 since this is the cell where the information lies.
I've tried just adding the sheet name like this...
=IFERROR('Install Data Pivot'!(ADDRESS((MATCH($A2,'Install Data Pivot'!$A:$A,0)),MATCH(B$1,'Install Data Pivot'!$4:$4,0),1,1)),0)
But that doesn't work. I've also tried using different variations of Indirect and Index but I still can't get it to reference the other sheet.
I have a cell, lets call it A1, w/ an IF statement referencing another cell, which is a function of two values taken from RTD. A1 is either blank or reads "One." I want another cell to tally the # of times that A1 takes on the value "One."
The issue:
I've tried approaching this by using Worksheet_calculate and an IF/Then statement to add 1 each time Range("A1") = "One" however this doesn't work because it keeps adding 1 while A1 reads "One." The calculations seem to fire off every milisecond, so if "One" flashes for 0.5 seconds, I get 500, and if it flashes for 3 seconds, I get 3000. For both cases, I'd like it to just add 1.
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
Custom average function. can this be done with Worksheet functions:
View 4 Replies View RelatedThis 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.
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.
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)
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
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.
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 RelatedI 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 RelatedDoes 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 RelatedI have a custom function that will tell me which quarter it is based on a date entered into a cell. It works except if the cell is empty is still returns the last Case but not my Case else. If the cell is blank I wanted the function to not return anything.
Function QuarterMonth(InputDate As Date)
Dim MonthNumber As Integer
MonthNumber = Month(InputDate)
Select Case MonthNumber
Case 1
QuarterMonth = "Q1 - 13"
[Code] ...........
I am trying to use the AutoFilter/Custom function in Excel (it is available under the Data Menu). It offers me two conditions/criteria that I can apply using and/or. For eg:
Filter:
does not begin with - 3
and / or
does not begin with - 9.
I want to add a third 'and' criteria .. is it possible, and if so, how?
The column that I am trying to filter has numbers formatted as text.
I was using the macro provided by shg in post#6 of this thread: Interpolate Two-dimensional Array? ...
View 6 Replies View RelatedA 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
I have a User Defined Function, one section attempts to clear the contents of some cells but it doesn't? (I've commented the line in question).
Function getCommission(rng As Range) As Currency
Application.Volatile
Application. ScreenUpdating = False
If rng.Offset(0, -1).Value <> "Yes" Then
Dim numTrucks As Long
numTrucks = rng.Offset(0, -10).Value
On Error Goto zero
I have created an addin (myAddin.xla) with custom functions in it. In a given cell, my funtion would look something like "=myCustomFunction(A1, B5)".
Periodically, while I'm working with the spreadsheet, I'll do something to a cell and the formulas in all the cells which used a custom function immediately get reset to something along the lines of "=C:...Application DataMicrosoftAddInsmyAddin.xla'!myCustomFunction(A1, B5)". The reset function also causes the function not to work properly and I get a "#NAME?" error in the cell.
I haven't yet figured out what sequence of actions causes the reset (it happens infrequently, but often enough to be a major problem) but it happens while I'm working with the document (i.e., it doesn't seem to be linked to saving/closing/reopening/etc). After I notice the reset, if I go to each cell that uses a custom function and delete everything from "C:... to myAddin.xla!" in the formula bar and hit enter, the function works normally as before.
(Technically, my Windows XP Pro system uses synchronization with a server at work, so the file path is not "C:...Application DataMicrosoftAddInsmyAddin.xla'!" but rather "\myServerNamemyDirectoryDocuments and SettingsmyUserNameApplicationDataMicrosoftAddInsmyAddin.xla'!". I'm not sure if this reset error has anything to do with being connected to the server or not, but figured I'd mention it anyway.)
i have a function ("function1") that takes a range as an input:
function1 (a As Range) As Double
i have another function ("function2") that internally creates an array "a" that I need to be the input for function1. I tried, inside function2,:
...
function1(a)
...
but of course (?) it does not work...