Tracker
HOME    TRACKER    Excel
 Advertisements:

# MOD Function In VBA

## 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.

Sponsored Links:

Related Forum Messages:
Conditional Formatting: OFFSET Function To Define A Range Inside A SUM Function
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?

Function To Mirroring Cell Relative To Function's Location
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?

Nested IF Function Error (entered Too Many Arguments For This Function)
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")))

Force Function To Use Row Relative To Cell Housing Function
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.

User Defined Function Function Tooltip
when 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?

2007 Right Function With Embeded Find Function
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)).....

Concatif Function Breaks Beforedoubleclick Function Just By Being There?
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).

Show Insert Function Dialog Pre Set To 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)

AVERAGE Function Embedded Within An IF Function
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

Vlookup Function Together With An Offset Function
I 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.

Write The VBA Function Through The Function Wizard
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.

Work Around To The 7 Function Limit With The IF Function
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.

DATE Function & IF Function (return A TRUE Or FALSE Based On A Date In A Cell)
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?

Contains Function: Function That States If A Cell In The Email Column Contains A String Or Value From The Names Column
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.

Function Back Variables: Function Give Two Or More Output Variables
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.

Use The OR Function Inside An IF Function
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")

Changing A VBA Function Into An IF Function
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"

Rand() Function In Another Function
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?

Sum Function With Address Function
On 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

Replace Function "Sub Or Function Not Defined"
I'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.

VBA Round Function Vs Worksheet Round Function
I 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??

VBA GPA Function
Currently I am using this formula =AVERAGE(CHOOSE(MATCH(A1:E1,{"A+","A","A","B+","B","B-","C+","C","C-","D+","D","F"},0),4,3.8,3.6,3.4,3.2,3,2.8,2.6,2.4,2.2,2,0)) to calculate the GPA's and it works fine. What I am trying to do is to wite a function for GPA's and I am coming up short. I am still trying to learn VBA can someone please guide me. Thanks in advance Stephen.

Function gpa(r As Range) As String

On Error GoTo Fail

For Each cell In r
Select Case UCase(Trim(r))

Case "A+": gpa = 4#
Case "A": gpa = 3.8
Case "A-": gpa = 3.6
Case "B+": gpa = 3.4
Case "B": gpa = 3.2
Case "B-": gpa = 3#
Case "C+": gpa = 2.8
Case "C": gpa = 2.6
Case "C-": gpa = 2.4
Case "D+": gpa = 2.2..........

Getting The Row Function
If I'm using the small function. How do I get the row instead of the actual value?

What Does The Dir Function Do?
What does the Dir function do? I've used the object browser and help but I still dont get what it does!

Between Function
Between function in excel ...

MID Function
I have a column wiht a series of characters like this:

ABCDEF+966789

The column is repeated like this all the way down but with different numbers.

How do I correctly use a formula like this:

=MID(A1,5,255)

I only want the characters on the right side to show up in the row.

Function In VBA
why the below code does not work?

Sub AnsiSub()
ActiveSheet.Cells.Select
Selection.Formula = Value(Trim(Substitute(A3, char(160), char(32))))
MsgBox ("Complete")
End Sub

IF/AND Function
I have data relating to staff members and application outcomes. I need to total the instances of each outcome for each staff member in another table. So for example if one column reads "John Smith" and the next column reads "offer made" i need to total the number of instances that that appears in the data.

IF And SUM Function
I want to do is take a number from column A (the player level) and do a sum function in column B from the current level to 19 (columns B,C,D,E represent seperate guilds and exp required for that level/guild).

So it will look like this (underline=input, bold=return output):

Current level: 12 EXP to 19: (b12:b19)

11,0001,0001,5005,000
21,6003,2004,80016,000
33,4006,80010,20034,000
46,00012,00018,00060,000
59,50019,00028,50095,000
614,00028,00042,000140,000
719,60039,20058,800196,000
826,40052,80079,200264,000
934,50069,000103,500345,000
1044,00088,000132,000440,000
1155,000110,000165,000550,000
1267,600135,200202,800676,000
1381,900163,800245,700819,000
1498,000196,000294,000980,000
15116,000232,000348,0001,160,000
16136,000272,000408,0001,360,000
17158,100316,200474,3001,581,000
18182,400364,800547,2001,824,000
19209,000418,000627,0002,090,000
Totals
1,264,0002,527,000 3,790,500 12,635,000

Using Len() Function
I'm having a problem using Len() function. I am trying to count the number of characters in an range using an array function.

{=SUM(LEN(CTFUpload))}

The problem arises with dates. I have the dates in the format dd/mm/yyyy which is ten characters however the len() function counts it as 5 characters i.e. it counts its numeric value.

The character count is based on how it will appear on a txt file.

Min Or Max Function?
i dunno what function i should use, in this kind of situation.

if there is value in Cell A5 , Cell A7 should return the value in A5.

Cell A1 12pm
Cell A2 6pm
Cell A3 9pm
Cell A4 12am
Cell A5 8am

Cell A7 8am

however, if there is no value in Cell A5, Cell A7 should return the value in A4(the previous value before A5).

Cell A1 12pm
Cell A2 6pm
Cell A3 9pm
Cell A4 12am
Cell A5

IF OR Or AND Function
I am using this formula: =IF(OR(B14="",C14=""),"",C14-B14)

To say if Cell B14 or C14 are empty, make the cell with the formula in empty as well but it still tries to calculate of either B14 or C14 have a value in

I want it to only calculate if there are two valid values in B and C

Min And Max Function
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.

Function Vs. Sub
I was writing a procedure that has 4 inputs and returns an array(double).

My question is when should you use a function and when should you use a Sub procedure? I have read over the documentation and is seems that the main difference is:

Function

1. you can use it in the workbook, and you can't with a "sub" procedure so in cell A1 you can write "=FunctionProcedure1(B1)"

2. in a function you cannot change some objects, like ranges, so you cant do as much.

3. You can neatly set a variable equal to the function, like Dim xx as double,arg1 as double

xx = FunctionProcedure1(arg1)
...
and for a sub procedure to return the value, you would need to write
Dim xx as double,arg1 as double
Call SubProcedure1(arg1,xx)
'and define the variable "xx" in the sub procedure

UDF Function
I 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.........................

Int Function
I am experiencing very strange behavior with Int function within VBA. Look at this
Sub a()
Dim k As Double
k = 3.05
MsgBox Int(k * 100)
End Sub

For me it prints 3.04 instead of 3.05, why is it so?! I am amazed with such a behavior.

VBA Function To Sub
I have a user-defined type as follows:

Type DocketIdent
cStore As String
cSuburb As String
cPostcode As String
cCheckOutID As Integer
cDatePurchase As String
cTimePurchase As String
cInvoiceNo As String
cNoOfItems As Integer
End Type

I have a function that uses a DocketIdent variable and assigns values as shown above to it. i.e. cStore, cSuburb etc.

I'm trying to return these individual Docket details from a Function to a Sub procedure.

Is this possible and would I have to return each field of the type individually?

Function
f.GIF

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

If Function
I have a query regarding if function. please see the example below:

Col1 Col2 Col3 Col4 Col5
1 10 20 30
22 11 21 32
14 12 22 39
35 13 23 32
15 14 24 27

My question is how to find out

if value of Col1 is between col2 & col2 then put "A" in col 5 in the corresponding row, if value of Col1 is between col2 & col3 then put "B" in col 5 in the corresponding row and if value of Col1 is between col3 & col4 then put "C" in col 5 in the corresponding row.

MOD Function
what the hek the MOD function is supposed to do. According to the MS Help file, it returns the remainder of a number divided by another number. Now, when I was in school, the remainder was pretty much anything after the decimal. That doesn't seem to be the case in Excel, however.

For the examples here, let A1 equal 5.

The formula =MOD(A1,2), as I see it, should return 5 since 5/2 equals .50, but it doesn't; it returns 1.
Further, 5/3 equals .67, but =MOD(A1,3) returns 2 (what, is it rounding?)

There's a lot of talk about MOD being used to return even/odd results, but does this work when the MOD function uses 1 or 3 (or higher) as the divisor?

Function Instead Of One Within Vba
I try to use a worksheet function instead of one within vba.

Here's what I want to do:

Return the value from a cell within a vertical range named "Phases Active." The value to be returned is the one in the same row as the non- blank cell 1 row to the left of this range. So, I can use "x" to make a selection of the range basically. VLookup, right?

=VLOOKUP((ISBLANK=FALSE),PhasesActive,FALSE)

OR Function: Get Either B-A Or D-C Or (B-A)+(D-C) As A Sum
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".

IF Function More Than, Less Than
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")

IF Function - Last Value
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)))

OR Function Together With IF Function
Basically i have this equation here,

=IF(OR(C7>22.5, D7>12.5, E7>10.5), IF(F7>=50,IF(F7>=60,IF(F7>=75,IF(F7>=95,"HQ","GQ"),"AQ"),"AO"),"RQ"), "RQ")

the IF statement in the middle is fine and helps me get
VALUES
RQ0-50
AO50-60
AQ 60-75
GQ75-95
HQ95-100

Which is all correct. But here's the problem. When C7 is under 22.5, or when D7 is under 12.5 or when E7 is under 10.5 i need to get a value of RQ. But at the moment this isn't working. I put in the OR statement for this purpose. However even if my D7 Value is under 12.5 for example, I'm not obtaining the intended RQ value.

If Function With And?
I want to correct the formula in J3 so it gives me the value in H3 but only if F3 is blank and H3 is less than or equal to 19.

Using A LIKE Function In A Formula
Is anyone aware of a LIKE style function in a formula where the formula would check some text and if it was similar to another text if would populate a chosen answer?

An example being asking the formular to look at Winterhur and Wintahur and as they are similar populating a chosen answer...

I've thought about wildcards but not sure it would work in this case

IF Function With Dates
The worksheet will have a list of staff and the date they sat for a particular test. I need to be able to highlight in some way when for example 6 weeks has passed so that the staff member can be re-tested.

We want to be able to open this worksheet and see immediately any staff who are overdue for a retest. If they have undertaken such retesting we obviously don't want to see them red flagged or whatever any more.

Do While Loop With If Function
I am trying to "reverse the scoring" in a selected row of data. that is if the respondent selected 1 (on a scale of 1-4) I would like it to be replaced with 4.

I have done up this code (which might be pretty messy) but I always end up with Compile error: Loop without do.

Sub ExecuteConversionP2()
'Checks to see if the macro has already been run
Sheets("Sheet name").Cells(72, 3) = Sheets("Sheet name").Cells(72, 3) + 1
If Sheets("Sheet name").Cells(72, 3) > 1 Then
Exit Sub
'if it has already been run the cell C72 will have a number in it and it will exit
Else
Sheets("Sheet name").Select

'This is where I want the loop to start
Range("D76").Select
Do While ActiveCell.Value ""...........

Copyright © 2005-08 www.BigResource.com, All rights reserved