Determining Data Type Of Argument In Custom Function

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


ADVERTISEMENT

Function Argument

Sep 25, 2008

how to create a function argument containing my own defined formula?

View 9 Replies View Related

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 View Related

Multi Argument IF Function

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

Return Function As An Argument

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

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 View Related

Setting If Function Argument In Macro

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

Dir Function - Invalid Procedure Call Or Argument

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

Using Multiple Criteria In Second Argument Of CountIfs Function

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

Reading Function Argument From Separate Cell?

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

Define Argument Prompt On Function Module

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

Preventing A Function Being Called If Argument Is Unchanged

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

Invalid Procedure Call Or Argument In Function

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

Translating Argument Of A Function Between Versions Of Excel

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

Using Array For Column Or Row Argument In INDEX Function?

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

Excel 2010 :: Data Validation With Custom Function And Message Box Display

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

Excel 2007 :: Determining / Counting Number Of Values Used In SUMIF / SUMPRODUCT Function

Aug 2, 2014

I am using Office/Excel 2007 and Windows 8

I want to determine the number of values SUMIF/SUMPRODUCT functions used/checked to arrive at the answer, i.e. 95 for row 2 and 116 for row 3. A function that can give a 3 and 2 respectively.

Example:

Results for Row #2 gives: 95 with 3 values picked up under "Rate"

Results for Row #3 gives: 116 with 2 values though three places were checked up under "Rate"

How can I count the number of these values referencing at the "Rate" like in the functions used.

I managed to use SUMIF/SUMPRODUCT functions to calculate totals based on "Rate" in row one.

I tried COUNTIF function, but allows only field/heading.

A
B
C
D
E
F
G

[Code] ..........

results for each formula used

95 formula: =SUMIF(B$1:G$1,B$1,B2:G2)

95 formula: =SUMPRODUCT((B$1:G$1=B$1)*(B2:G2))

3 formula: =COUNTIF(B1:G2,B1)

View 9 Replies View Related

"wrong Data Type Error" Public Lookup Function

May 4, 2007

I have attached a file called Test2 which has a public function called DBLookup. The function looks in the attached access database simulating a Vlookup just like in Excel. The problem I am having is when that data appears on sheet1. I am getting #VALUE in the cell and the erroe states "a used value in the formula is of the wrong data type".

my

Dim adoCN As ADODB.Connection
Dim strSQL As String

Const DatabasePath As String = "C:Test2.mdb"

'Function argument descriptions
'LookupFieldName - the field you wish to search
'LookupValue - the value in LookupFieldName you're searching for
'ReturnField - the matching field containing the value you wish to return...

View 5 Replies View Related

How To Make A Custom Conversion Program / Formula Between Custom Data

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

Determining Which Column Data Is Located In

Apr 2, 2007

I have a table of data, every entry in the table is different. I need a way in a single cell to determine what column the data is in (and a way to get the row it's in would be nice too), some way to do VLOOKUP and HLOOKUP in reverse.

In the attached example I need a function that can take dog and return "1", "A" or even the header "Animal", if another similar function can return the row, that would be nice also but if I can get the column I can get the row. Example

I've tried a few things with creating an array formula to do matches across multiple columns and I can make a multi-cell array that will flag the right column with the right column number, but any attempt to sum/max/match this array formula returns 0 or #N/A

View 5 Replies View Related

Determining Formula To Choose And How To Input The Data In Correct Fields

Aug 8, 2014

I am trying to save some time converting a cut up inventory catalog number. Specifically focusing on the color code. I have the list of colors associated with their specific part number. The order cannot change or my sheet will mismatch.

What I want to do is convert the # in column A to the color code text in column b so that it matches the translated code in column C? For example

1 I want to tell 1 to = Polished Chrome anytime #1 appears in my column.

What formula should I choose to achieve that goal. As well, how do I know what data to imput into the formula wizard?

Sheet is attached.

I have Cut a few thousand items off the bottom of the sheet. I can apply to additional data left off.

View 4 Replies View Related

Mode Function For Each Work Type

Sep 14, 2009

I’m using excel 2003 and I need help with the MODE function. I have a spreadsheet with 142 work types’ rows of work with each one showing the last 13 months of work received. I have set up a mode function for each work type, but for the ones when excel cannot work out the mode it puts n/a in and I have to do them manually, by looking at the last 13 months data and basically guessing a mode figure – which is a pain (e.g. if there was 13 different numbers from say 120 – 135 excel would give n/a but I would put the mode at around 128. As this spreadsheet gets updated monthly is there a way I can trick excel into giving a mode figure, or a figure close to what the mode should be. I have attached a sample of the spreadsheet.

View 3 Replies View Related

Lookup Function Returns Type Mismatch?

Feb 23, 2014

I get a Type mismatch from this line:

x = Application.WorksheetFunction.Lookup(2, 1 / (Range("G7:P7") = "A"), Range("G7:P7"))

View 1 Replies View Related

Error '13' Type Mismatch In Function Code

Oct 6, 2006

while the code works fine by itself in the sample file, when inserted into my other file which contains another set of algorithms, it generates a run time error '13' type mismatch whenever the 'Hide' button is clicked.

this line of the following function: b = x / tpi

Function range(x)
tpi = 2 * 3.1415927
b = x / tpi
a = tpi * (b - Int(b))
If (a < 0) Then a = tpi + a
range = a
End Function

View 9 Replies View Related

VBA Function For &quot;Mean&quot; Using Array As Argument

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

Type Mismatch '13' On Passing Function Result To Variable

Aug 11, 2006

I have the following codes in my spreadsheet, they are pretty much the same and both used to work perfectly. However, now the first code returns a Type Mismatch '13' on line beginning mynum= and the second one works perfectly.

Sub addtasks()
Application.DisplayAlerts = False
myrow = Cells. Find(" Total P&C Estimate").Row - 3
mycell = Cells(myrow, 2)
mynum = Right(mycell, Len(mycell) - InStr(mycell, "#")) + 1

With Range(Cells(myrow, 2), Cells(myrow + 2, 2))
.EntireRow.Copy
.EntireRow.insert Shift:=xlDown
End With

Application.CutCopyMode = False
Cells(myrow + 3, 2) = "Task#" & mynum
Application.DisplayAlerts = True

End Sub

View 8 Replies View Related

Lookup Type Function Returning Multiple Values

May 23, 2007

I have attached a sample spreadsheet which describes my problem.

In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.

View 5 Replies View Related

Custom Function To Run A Macro

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

Custom Average Function

Dec 11, 2009

Custom average function. can this be done with Worksheet functions:

View 4 Replies View Related

Custom Function For An Algorithm

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







Copyrights 2005-15 www.BigResource.com, All rights reserved