Changing Cell Data By Custom Function
Aug 5, 2008
I 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?
View 8 Replies
ADVERTISEMENT
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
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
Feb 8, 2013
Basically 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
View 2 Replies
View Related
Apr 27, 2008
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
View 9 Replies
View Related
Mar 5, 2008
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 Related
Feb 24, 2014
I have four cells c1 thru c4. The SUM of these cells must equal 1. I want to display an error message "sum of cells not equal to 1.0" but only when data has been entered in all four cells.
For instance =SUM(C1:C4, "<>1") should display the error message. Is there a way to write the formula with an IF statement and still use data validation with error message box?
Assumptions:
-Will ignore blanks
-Error message "Sum of cells are not equal to 1.0" until values in all four cells have been entered, not after a value is entered in a cell. (validate expression after all four cells have values or blank)
Scenarios:
c1 = .25, c2 is blank, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .25, c3 = .25, c4 = .50 TRUE no error message should displays
c1 = .25, c2 = .30, c3 = .25, c4 = .50 FALSE error message should display after value is entered in c4
etc
What I don't want is for the error message box to display after .25 is entered in c1 because the user is still in the process of adding values. Can this be done using Data Validation in Excel 2010?
View 2 Replies
View Related
Jul 26, 2006
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 Related
Oct 2, 2007
I 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
View 6 Replies
View Related
Mar 6, 2014
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.
View 3 Replies
View Related
Jul 3, 2009
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.
View 3 Replies
View Related
Sep 30, 2006
I'm trying to create a simple VBA function in Excel so that I can sort and calculate results based on various conditions. I can get everything to work except changing the color of the cell where the output is placed. Here is what I have so far:
Function nameOfMyFunction(arguments)
If conditions Then
nameOfMyFunction = "No GRP"
ActiveCell.Font.Color = RGB(255,0,0)
End If
I'm sure that its something simple, but I've spent long enough on my own trying to figure it out and nothing seems to work.
View 5 Replies
View Related
Mar 27, 2013
I am trying to get a phone number column to change between two custom styles. I have added the styles in as:
Mobile: 0000 000 000
Extenstion: x00000
I need column E5 down to change between the two if < 6 or >5 numbers are used in a cell to change to the correct style.
Does this can be done just using the styles or vba coding is required.
I already have some vba to add the date into column B if column A is altered.
View 2 Replies
View Related
Feb 20, 2007
I have a small problem using the ‘sumproduct function’ which I am using it to calculate the area under curves using the following equation (=SUMPRODUCT((B10:B109-B9:B108)*(D10:D109+D9:D108))*0.5). However I have hundreds of curves to calculate the area under and the length of the data series for each curve is different, I am currently changing the length of the data series by hand. I have attached an example worksheet of 2 sets of data (the formula I am using at the moment is highlighted in blue – row 203). Is there are function or formula that I can use inside this equation to change the range of the formula depending on the length of the data series?
View 2 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Apr 12, 2012
I'm working in Excel 2010. I'm trying to create a dynamic range, using the OFFSET function. I've got it working beautifully but as soon as I save it and close the Name Manager the range stops working. When I go back into Name Manager I find all the cell references have changed (from C1:C600 to C1048572:C595 - or some other strange range).
P.S. What it's doing isn't so important as when the cell references are right it works. But just for full info: It's an OFFSET function, starting at A1. It will look at a list of companies and use a Match function to find out how many rows to offset (based on where the first instance of a particular company. And it uses the COUNTIF to make the range the same number of rows as there are entries for that company.
Here is my broken function:
=OFFSET('Map Point'!A1048572,MATCH("Company Name",'Sheet Name'!C1048572:C595)-COUNTIF('Sheet Name'!C1048572:C595,"Company Name"),0,COUNTIF('Sheet Name'!C1048572:C595,"Company Name"))
View 3 Replies
View Related
Apr 30, 2014
I often use the same file/pivot table for both month and weekly reporting. In my source data my field headers show 1,2,3,4..etc. This way I can use for month or weekly. But I often change the "Custom" name on the Field Value Settings to something more descriptive like "Jan" or "Week1"
The problem is that each time I update the the "Custom" name the Pivot Table auto refreshes and it takes forever to re-label all the columns.
Is there a way to suppress this refresh when updating the Custom Field Name?
Capture.JPG
View 2 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
Aug 4, 2014
cell a1 has yes cell b1 has yes and i want to type yes in c1 that will change a1 and b1 automatically to a blank cell
View 3 Replies
View Related
Mar 5, 2009
I require a custom data validation formula to limit cell value to maximum of 4 decimal places.
0.0001 ok
0.02 ok
0.3 ok
0.12345 fail
0.123456 fail etc
View 2 Replies
View Related
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
Nov 5, 2012
I 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] ...........
View 9 Replies
View Related
Feb 3, 2010
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.
View 9 Replies
View Related
