# MOD Function In VBA

Feb 6, 2010In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.

In the excel worksheet, I use MOD(YEAR();4) to determine whether there are 365 or 366 days in that year. I would like to use this function also in my User Defined Function VBA Code. I cannot find how to do this.

I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:

[Code] ........

When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.

Another interesting dilemma to solve. Using this formula:

View 2 Replies View RelatedFunction Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?

Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double

Dim temp As Double

[Code]....

In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?

View 9 Replies View RelatedI have a function

VB:

Function f1(Matrix As Range)

'Does something and returns f1 = a double

End Function

And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:

VB:

Function f2(dD As Double)

Dim MatrixRed() As Double

Redim MatrixRed(1 To dD, 1 To 10)

For i = 1 To dD

For j = 1 To 10

MatrixRed(i, j) = i * j

Next

Next

f2 = f1(MatrixRed)

End Function

I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?

let me start by saying that I know an example workbook would be useful here, but the part I'm struggling with is the [managementroster.xlsm] file, and there is A. no way I can release it to the internets and B. its so huge/complicated I couldn't even begin to reproduce a portion of it, scrubbed of data, and hope to maintain its functionality in a meaningful manner.

[Code]....

This formula checks a staff number on this spreadsheet, and then goes and looks at the staff number on the roster. Once found, it returns that staff members roster, but changes any manager codes in the MRC list to Mgr, and changes all other roster codes to Free.

I now need this formula, before altering roster codes to Mgr or Free, to only return codes that are a match for another table (or after really. I don't particularly care, so long as only codes are shown that match data from another table). I think an index/match function would do the trick, but this forumula is already at the edge of my excel ability, nesting another function within it is completey beyond me. The relevant cells for the index/match function would be:

This first Match function targets the column. $E3 is the date required, $BA$1:$DN$1 is the range the dates are entered in

Match: Lookup value = $E3

Lookup array = '[ManagementRoster.xlsm]Vacancies!'$BA$1:$DN$1

match type = 0

This second Match function targets the row. $A$4 is the department name, $B$434:$B$452 is the range where all departments are entered

Match: Lookup value = $A$4

Lookup Array = '[ManagementRoster.xlsm]Vacancies!'$B$434:$B$452

match type = 0

Index: array = $BA$434:$DN$452

So I think my final function is

[Code] .....

But I have absolutely NO idea where it would fit within my first formula, or how to code it so that my original formula only reproduces results that are found in both sheets, or anything.

I have created a List in excel of various tasks. (See attached example.) Each row contains one cell with a function which equals the cell above it and to the right. So the function for cell C3 would be D2. The only importance is that the cell mirrored is always the cell one above and to the right. However, now I would like to be able to rearrange this List, but the “one up and to the right” function will now be all over the place.

Is someone aware of a function which will target a relative location instead of an exact cell?

I keep getting the "You've entered too many arguments for this function" error.

Here is the formula:

=IF(B15=D40,E40,"",IF(B15=D41,E41,"",IF(B15=D42,E42,"","Invalid Shipping option")))

I am trying to nest an IF function with a CEILING function. If C10 is < 3.5, make it 3.5, however, if C10 > 3.5, CEILING (C10, 5)

right now it looks like:

If (C10

I have been looking around have this much code from this site, modified. What I am trying to do is calculate numbers by a position with cells D through K having numeric values. I have 'hardcoded' the cells (D2, E2, etc in the code below), but in reality I only want the current row (so if the formula is on the 2nd row, I want D2, if it is on the 3rd row, I want D3).

My problem is obviously the formula isn't working because I am not correctly tying back to the spreadsheet (Positioncalc.xls). When I put the formula in the spreadsheet it works, but in my script I get 0 everytime.

My Script:

Function Position(rCell As Range, Optional RightPosition As Boolean)

Dim vResult

Select Case rCell.Text

Case "QB"

vResult = (2*D2) + (2*E2) + (2*F2) + (4*G2) + (2*H2) + (1*I2) + (4*J2) + (3*K2)

Case Else

vResult = "Invalid Position"

End Select

If RightPosition = True Then

Position = vResult

Else

Position = "Position not valid"

End If

End Function

So, when I put =Position(A2,True) I expect to see the formula results of those cells calculated based on the position (QB, HB, etc with their unique formulas).

My next challenge after this is to highlight certain cells based on the Position. So if A2 = QB, I want cell D2 boldface and Red, etc. I have seen some scripts on colors and such here, so I might be able to figure it out.

I have a range of cells, for this example I will use 2.

Cell E17 = 77/170

Cell E18 = 8/9

Using the following formula: =SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)))

This bring back an #VALUE! Error as the second part of the formula keeps picking up "/9" however the first part works fine, displaying "170"

Now if I use:

=SUM(RIGHT(E17,FIND("/",E17)))+SUM(RIGHT(E18,FIND("/",E18)-1))

It all works. The problem is that I need this to be automatic using the above way means having to add a "-1" to every formula for a cell with only 1 char to be added.

Using the formula:

=SUM(RIGHT(E17,FIND("/",E17)-1))+SUM(RIGHT(E18,FIND("/",E18)-1)).....

I have a tracking sheet (attached) that has many functions, but I'm having trouble with two of them. First of all, I have a Worksheet_Change event set that when a cell is cleared, it fills the cell with a formula to use the record above it as a default value if another cell is equivalent. This worked when I first wrote it, but now it seems to break every other time I use it. When assigning the formula it returns a Method Default/FormulaR1C1/Offset failed error message. It seems to be different for each one, each time and I can't figure out what the problem is.

The other problem I'm having is that I wrote a BeforeDoubleClick event to expand or contract any given record, or series of records. this too worked when I initially wrote it, but now is only hiding one row when it should be hiding eight or more.

I'm not terribly versed in VBA and totally baffled as to why I'm having these problems. As I side note, any ideas to clean up any of the code and make this run smoother (as ther will be many more series added when complete).

I have a user defined function in one VBA module that I'd like to call from another module, so I would normaly scope it as "Public".

However, as I don't want it to be available to Excel as a worksheet function, I would also normally scope it as "Private"

Is there a way to have a function that is available to other VBA modules in the same project, but not to Excel as a worksheet function?

A most of time I'm using VLOOKUP function. I want to call specially this function by pressing custom button (w/o pressing "Insert Function" and choosing VLOOKUP)

View 4 Replies View Relatedwhen you type in a builtin function in the formula bar such as =DAY(), a small callout box appears below telling you the syntax - in this case "DAY(serial_number)". Is it possible to achieve this for a user defined function?

I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.

View 2 Replies View RelatedI am trying to use the vlookup function together with an offset function but i am not getting it to work properly.

The situation is as follows:

I have a column E in which i use the vlookup function to find its corresponding value in B which in turn refers to a named range. However, the figure i want the function to return is 2 columns to the right and 2 rows above the value which the vlookup funtion finds in the first column.

I have though of using the offset function, but i cannot figure out how to make this work.

I'm comfortable with the IF statements in excel and not familir with many of the other functions available to me. I am trying to create a character calulator for Elder Scrolls III (Marrowind). So that a person may enter their race, ***, and other choices and their by calculating all starting stats. There are 10 races you can choose from and I can cover 9 of them with the if function (leaving one as if all other options are false). I would like to know if there is a better way such as a drop down list or some way to search an array or anything that would be better suited to search the race cell and return data if it's true. I'm running into the same probably with choosing the constalation (13 choices in this catagory).

I thought to split it two different data fields, by letting you choose from 4 in one cell and the other 9 in a different cell, but realized I don't know how to make it check two different cells for a choice to return it to the display cell without hitting the limitation of the 7 nested funcations in a forumla again.

I am using the COUNTA function which works great but I also have data that I dont want counted and do not know of the problem solver.

E.G

in B1 I have vlookup function thats brings back either "B", "C" or "M".

in c1 I have =IF(A1="C",B1,"")

Now, that tells me if the data is B or not. If it is, it says B If it isnt, its blank

Thats great but when im trying to count how many "B"'s there are, it counts the blanks as well!

I am using COUNTIF functions with Defined Name lists to quickly determine if a certain number is on a list. As a backdrop, I am using Chemical Abstract Service (CAS) numbers and attempting to somewhat streamline chemical approval for a small company. CAS numbers are often in the format of XX-XX-X with varied amounts of numbers. One of the defined lists however does not have dashes.

Thus, I am using cell B1 to enter the CAS# once and then for each list having a column to itself with an associated worksheet with a defined name list. Most of the columns have the function =B1 with the conditional formatting of =COUNTIF(definedname,BX) and formatted to turn red if the chemical is on the list. This is working for all of the columns except for the list that needs the dashes removed. For instance, CAS 64-67-1 is put in B1 and cell B5 has the formula =SUBSTITUTE(B1,"-","") which brings the number to 64671 which matches the number in my defined name list. However, the cell will not turn red. What am I missing?

I want a UDF; = OK (f1,f2) each argument being an alternative function. If f1 returns an error message then use f2. Should be easy but I cannot get it to work.

View 5 Replies View RelatedI have the following function:

=IF(AND(I17>=$I$1,O17>=$O$1,Q17>=$Q$1,F17

TPR, DISPLAY and FEATURE columns generate a rating based off of an IF function. In the Executed column, I need TPR, FEATURE, DISPLAY to be averaged together...BUT....In I want the average only include columns where there are numbers. For example in row one the eqn would be (1+3+2)/3, but in row 2 the eqn would be (1+1)/2...can I state an average function within an if function? Or what would be the best way to create an eqn for this?? I have thousands of rows to complete and doing it manually is not an option.

0- Did not meet expectations

1- Below expectations

2- Met expectations

3- Exceeded expectationsTPRDISPLAYFEATUREExecuted?Effective?Comments132Coming off of a Dec promotion113111111221

On sheet 1 I have a list of 1000 firstnames

On sheet 2 I have a list of 1000 emails,

I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.

I am trying to return a TRUE or FALSE based on a date in a cell.

for example:

if cell A1 = 07/11/2009

I want A2 to show TRUE or FALSE if A1 is 14 days or more behind todays date.

I have tried stuff like:

=IF(A1=TODAY()-14,"True","False")

but it just always says false. EDIT: I have just noticed that if I change the date in A1 to exactly 14 days behind todays, it returns TRUE. So, it does work. Do I need to add a GREATER THAN in there?

Can a Function give two or more output variables. e.g.

Sub a()

x = 5

result = Y(x)

End Sub

Function Y (x As Integer) As Integer

Dim B

B = ... * x

Y = ... * B

this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?

I need this because function works with large matrix and I want to extract some values appeared in between.

I want to perform a very easy calculation, but i don't want that the calculation gets made if there is a 0 value within in. There are two values who are the main inputs if 1 of those 2 equal zero i don't want the calculation to be made, or when they are both zero. So far i came up with the following:=IF(OR(API167=0,API186=0),0,API167-API186)/(API167+API186)/2 that only works if one of the two values is zero.

Next question is that i want to sum up the row with the values calculated with the formula above, disregarding the zero values. This sum function must be devided by the amount of values which are used as input(so disregarding the zero's)

I am trying to use the OR function within an IF condition.

when I use each function seperatly, it works fine. but both functions

together always return the value of "B" - as false result, even when the

requested cell holds one of the true values.

for example:

=IF(B18=OR(148,150),"A","B")

I am using the following formula to calculate different criteria entered into one column - for example the user would either type Blue or Red into column H:

=COUNTIF(H2:H24,"Blue")

=COUNTIF(H2:H24,"Red")

Also, In column C, the user enters an answer of either 1 or 0.

What I have learned is each week I must also account for how many blue shirts were given the answer of 1 in column C as well as how many red shirts were also given an answer of 1 in column C. I do not need to bother with knowing how many received a count of 0. How would that formula read?

This would mean the formula would need to look at column H and see the answer of Blue, and look across the corresponding row to see if an answer of 1 had been given to that piece. If so, then count it; otherwise, don't count it.

i am using the following code to create a function in excel

i have to put the worksheets onto mac's so i cannot use a macro

can anyone help me change this into an iF function

load is cell D9 and the factor is in cell E9

Function Pmax(Load, Factor) As Double

Application.Volatile

Select Case (Factor)

Case "0"

Pmax = ""

Case "1"

Pmax = Load

Case "2"

Pmax = Load * 1.09

Case "3"

I want to call on the existing rand() function as a part of the function i've written, however I'm getting an error saying sub/function not defined when I go to compile. How do I have to define rand() so that vba knows that I want the use the existing function?

View 6 Replies View RelatedOn my worksheet =ADDRESS(18+BH8-1,60,4) gives me BH21. However = SUM(BH18&":"&ADDRESS(18+BH8-1,60,4)) gives me the #VALUE! error

View 2 Replies View RelatedI'm getting a Sub or Function not defined error on the code below. It highlights the Replace Function as the problem, have also tried the substitute function too with the same result.

View 11 Replies View RelatedI don't know if there is a setting I'm missing or I'm going mad but when I use the round function in VBA it doesn't round.

I am using Excel 2000. See the example attached.

In the cell A2 I have a value 0.525, cell B2 has a formula "=round(A2,2)" which = 0.53, but cell C2 is assigned via VBA ie

Sheet1.Cells(2, 3).Value = Round(Sheet1.Cells(2, 1).Value, 2)

and the result is 0.52??

I have data in columns b c d, in column be data is spreadout across different lines. Ie data in

b2

b34

b56

b70

b89

then i have data in every row in columns c and d, What I am trying to do is find the max in column c based on column b's entries

ie

find max between

b2

b34

find max between

b70

b89

let me just say the # of lines (data) in b are thousands.

A B C D

1 M1 Old M1 New M2 OldM2 New

2 0 000066 111632112188

I'm trying to get either B-A or D-C or (B-A)+(D-C) as a sum but not to include the sum where (B-A)<=0 or (D-C)<=0, but if both are <=0, then "ERROR".

i have a question which wants me to input a formula so that if the number is less than 2 in the cell "Day tripper" appears and if its more than 30 "too long" will appear. This is the formula i have got so far. =IF(I10="Less than 2","Day Tripper","Too Long")

View 2 Replies View RelatedI think this will be easier to understand if I just set up a scenario for you from scratch. Open a blank sheet. In cells B2:D10 enter random digits between 1 and 10. In B1 type in =SUBTOTAL(9,B2:B10) which should give you the sum of that column. Use autocomplete to fill in columns C and D with the corresponding formula.

I want a function to go in column A that will take the average of each row WHICH takes into account hidden columns. When I type in =SUBTOTAL(101,B1:D1) in cell A1, it returns a divide by 0 error. I understand it's because I'm taking a subtotal of a subtotal and that can't work. The formula works for the cells underneath, because rows 2 to 10 are filled with raw data. However, it doens't take into account hidden columns. So far, I've written a macro that finds the average and takes into account hidden rows. This is the code I've written, and I assigned it to a shortcut key.........................

f.GIF

i want to count that : A=1 and B="a" .

Attached is the file with a small sample of data.

I need to return a value in collum "F" for the longest time period available in columns B,C and D. I can get F4 and F5 to return the value from the corresponding longest period, but NOT row 6, which is slightly different.

A copy of the data is attached. THE FORMULA I'M USING FOR CELL F6 (and all others as well) is:

=IF(D6<>"N/A",D6,IF(D6="N/A",C6,IF(C6="N/A",B6)))

ADVERTISEMENT