Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  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.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Implement The Mod Function In VBA?
VBA 2003 does not support MOD (modulo arithmetic) as a WorksheetFunction, and there does not seem to be a direct MOD function in VBA. I've been dabbling with code that would do the same thing as MOD, so far with no success. Does anyone know how to do this in VBA? I assume it would be some combination of If...Then, Round (up or down), maybe a compare, and perhaps an iteration loop. I would think such a routine would be useful to many people.

View Replies!   View Related
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?

View Replies!   View Related
24 Digits Mod Function
how can i do this in excel 2002?

for example.....

mod(95000000922019182020281000,97)=24

but in excel im getting the value as 0
also when i type the 24 digits its show like ..... # NUM!

if i put the function like =A1-FLOOR(A1,B1) its working for 13 digits but not for 24 digits.

View Replies!   View Related
Mod Function Works Wrong
I was trying to explain modulus to someone and they wanted to know why you can "flip" symbols mod(-6,7) = 1 in Excel. So I got to explaining that -6 Mod 7 is the same as -6-(|-6/7|)*7 which is how you get 1.

And that's when I realized... |-6/7| = 0 not -1. Then I looked in VBA and sure enough -6 mod 7 = -6. Apparently the problem boils down to the Integer conversion. Excel is performing the integer coversion by rounding down (INT) wheras VBA appears to be using CINT.

So here is how it work out in excel:
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(-1)*7
-6--7
-6+7=1

But in VBA you get
-6-(|-6/7|)*7
-6-(|-0.857142857|)*7
-6-(0)*7
-6-0=-6

View Replies!   View Related
MOD Function: Color Every Second Row
I work for a training company based near Gatwick. We are currently producing a new course and one of the new functions we are using is the MOD function. We are using it to colour alternate lines (Conditional formatting). The formula works great, but we do have a few mathematical questions on it.

The formula is:

=MOD(Row()-C,N*2)+1<=N

The question I have is about the sections in red, why are they there and why do they make it work?

View Replies!   View Related
Mod Function For Comparing Looping Values
The following formula (thanks to Barrie) determines if the difference between 2 values equals a third value, within a fourth ‘margin of error’ value, and returns either True or False.

=AND(C1-D1

View Replies!   View Related
Combining Two Equations With MOD Function For UPC Check Digit Calculation.
Combining two equations with MOD function for UPC check digit calculation.
I wanted to combine these if possible,

View Replies!   View Related
MOD 10 Calculting
I have that formula on excell to calculate the MOD 10 value for a DIGIT CHECK.

BELOW THE FORMULA.

=10-MOD(SUMPRODUCT(--MID(A1,{1,3,5,7,9,11,13,15,17},1))+
SUMPRODUCT(--MID(A1,{2,4,6,8,10,12,14,16,18},1)*3),10)

It works for a number but it is not working for the other.

example datas : 5290000040000850120 /// THE RIGHT NUMBER IS : 2
it is ok for that number:

the other number :

5290000040000850044 // the right number : 4 after the 44

View Replies!   View Related
MOD Formula
Trying to find a formula in Colm. X that will take the negatives in column W and order in increments of 5.

Example - if the ending balance is negative 6, then order 10, if end balance is neg 12, then order 15, if end bal is -22, order 25.

Do I need a MOD formula? ....

View Replies!   View Related
MOD Func
The below is simple:

If I type 3 in A1 the formula returns 1. If I type 1 in A1 the formula returns 2. If I type 2 in A1 the formula returns 3.

I want the opposite. If I type 2 in A1 it returns 1. If I type 1 in A1 it returns 3. If I type 3 in A1 it retunrs 2.

******** ******************** ************************************************************************>Microsoft Excel - PPT.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCD13***2****31***Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View Replies!   View Related
Mod And Arrays
I am trying to fill calumn B with the data from an array. There are more rows than entries in my array and I want to loop back to the start of my array when it reaches the end of the array results.

Sub test()

Dim i As Integer, MyArray As Variant, RowCount As Integer, ArrayCount As Integer

MyArray = Array("test 1", "test 2", "test 3", "test 4", "test 5")
RowCount = Cells(Rows.Count, 1).End(xlUp).Row
ArrayCount = UBound(MyArray)

For i = 1 To RowCount
Range("B" & i).Value = MyArray(i - 1 Mod ArrayCount)
Next i

End Sub

View Replies!   View Related
Year Planner Mod
I have a copy of a year planner that calculates the days of the month and adjusts them according to the year input into the header area.

Would anyone please modify it so that the first column reads August and the last column reads July (instead of Jan to Dec) and still maintain the calculations as required?

View Replies!   View Related
MOD Operator Ambiguity
MOD Operator gives one result when this is used as worksheet function and the different one when I use that in VBA.

Cell A1 = 01-Jan-08
Cell A2 = 02-Jan-08

In Worksheet
=MOD(A1-A2,8) Gives 7

In VBA
Reminder = Range("A1")-Range("A2") Mod 8 Gives -1

View Replies!   View Related
MOD+ROW Conditional Formating
I have an excel document that has one columb (I) of cells that changes color depending on a specific date (columb h) . The cells that have conditional formating to change the row color based on where it is ( =MOD(ROW(),2)=1), stays blank and the proper color until columb A is filled in. The others show up red. How would I code/format the lines that aren't included in the conditional formating to always show white when no value is entered in columb a.

View Replies!   View Related
Insert Code Into This Workbook Mod
I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.

How would I go about doing that? I have this code so far to open the workbooks that are in the folder.

View Replies!   View Related
Passing Variables To Other Subroutines In Same Mod
I'm having issue with passing along a variable. I learned this morning how to pass these along. But for this instance something isn't working correctly.

The code is below:

I cut out all the subs in between so you can see the problem.I think it has something to do with "File search"

The problem is after sub New_Pro_Test runs then returns back to Sub RunFolder I get subscript out of range error. It is suppose to open the next workbook in that folder.

View Replies!   View Related
Change MOD Divisor On-the-fly
I have data in column C starting cell 2 that runs sometimes to 50, sometimes 80 or more rows. I would like column D to MOD the numbers in C, but in cell E2, I would like to enter the MOD divisor and have Excel use whatever number I put into E2 to MOD column D. What formulas would I use for this? I've attached an eg in case I am not clear.


View Replies!   View Related
How To Calculate Check Digit (MOD 10)
How do I create a formula that will calculate a check digit for a 9-digit number using MOD 10? The weighting is 212121212. The problem I am having is that if the sum of the product is in double digits, they must be treated as individual digits.

Example:

Number To Be Calculated: 0 0 4 4 5 5 5 2 5

Weighting: 2 1 2 1 2 1 2 1 2

Sum: 0+0+8+4+1+0+5+1+0+2+1+0

22/10 = 2 r2
10-2 = 8 (Check Digit)

Is it possible to create such a formula? I need to calculate several hundred and do not want to do this manually!

View Replies!   View Related
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.

View Replies!   View Related
INDIRECT(MOD(ROW)) Skipping A Lot Of Rows
I'm currently working on a report and what I'm trying to do is get a Row of information to pull into 4 rows. My current formula looks like this:
=INDIRECT("'Paste SAP'!H"&IF(MOD(ROW()-1,4)=1,ROUNDDOWN(((ROW())+3/4),0)," "),1)

I change the bolded number to correspond to which row (1,2,3,0) but it's not functioning. I've done it with other but for some reason this one doesn't work. I've attached the template so you can see what it looks like. The problem is with the SAP Tab and the info from the Paste SAP tab.

View Replies!   View Related
Recursive Division Macro Mod Needed For Char Type
I need a slight mod to the below code which works perfectly otherwise. What it does is it divides whatever is in column K by 30 and puts in its relative cell in column M( There a bunch of mini tables below each other with blank rows in between).

The thing is sometimes there are characters in column K like "N/A" for instance and the macro crashes since its only designed to take into account numerical and blank cells in column K.

View Replies!   View Related
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"

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

View Replies!   View Related
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?

View Replies!   View Related
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)

View Replies!   View Related
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??


View Replies!   View Related
CountA Function Using VBA
How can I make it work ?

Sub test2()
Dim Wbk, aaa, bbb
Wbk = "\server1folder1file1.xls"
aaa = Application.WorksheetFunction.CountA(Workbooks(Wbk).Sheets(1).Range("A:A"))
bbb = MsgBox(aaa)
End Sub

View Replies!   View Related
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..........


View Replies!   View Related
WorkDay Function VBA
The function is to show the next workday date in cell a1


View Replies!   View Related
#value Error In VBA Function
my formula called ' conversion' used to convert foreign money in EUR does not
seem to work. My formula gives a value error. I wonder why...


Function Conversion(Position As Integer, Currency As Integer) As Integer

Dim USD As Integer
Dim GBP As Integer
Dim CHF As Integer
Dim JPY As Integer
Dim NOK As Integer
Dim NZD As Integer
Dim SEK As Integer
Dim ZAR As Integer
Dim ISK As Integer
Dim TRY As Integer
Dim AUD As Integer

' here i give a destination to my forex quotes who are in a excel sheet
USD = Range("N9").Value
GBP = Range("N10").Value
CHF = Range("N11").Value
JPY = Range("N12").Value
NOK = Range("N13").Value
NZD = Range("N14").Value
SEK = Range("N15").Value
ZAR = Range("N16").Value
ISK = Range("N17").Value
TRY = Range("N18").Value
AUD = Range("N19").Value

View Replies!   View Related
VBA Function Not Working
I copied this function from a VBA book and I get a Error 91 "object variable or with Block variable not set"

I have made sure that Microsoft scripting is referenced.
any clues would be appreciated.
I might add that this is a follow up on a thread I discussed with "Datasmart", thanks to John for getting me started.

{code]
Function BrowseForfolderShell() As String
Dim objshell As Object, objfolder As Object
Set obshell = CreateObject("Shell.Application")
'Uncomment next line to start at desktop
'Set objfolder = objshell.BrowseForFolder(0, "Please Select a Folder", 0, 0)
Set objfolder = objshell.BrowseForFolder(0, "Please select a Folder", 0, "c:")
If (Not objfolder Is Nothing) Then

View Replies!   View Related
VBA IF Function Or If IsEmpty
I need a IF funtion to look at a series of cells and if the cells are empty I want it to proceed to userform1. If the cells have data already i want it to go to userform5.

I tried IF IsEmpty(cell list)=True Then UserForm1 and IF IsEmpty(cell list)=False Then UserForm5

But it only bring up userform5 no matter what the cells have in them.

View Replies!   View Related
Yield Function In VBA
I just upgraded to Office 2007 and am having trouble getting the Yield function to work in a macro I wrote in office 2003. I have added the analysis tool pack and the analysis toolpack for VBA and it still does not work.

The error I get is that the object or property is not supported. All variables used in the function are assuming the correct values.

MarketYield = Application.Worksheetfuntion.Yield(Date1, BondMaturityDate, TotalWAC2, MarketPrice2, 100, 2)

View Replies!   View Related
VBA Left Function
I am trying to use the left function to include only the first account number, for example if a cell has "120122 280000" (there may be many spaces between the two numbers), I only want 120122.
So far my programming only returns the 280000 into the cell.

I am working with cell values in column "K".

View Replies!   View Related
VBA Simple Right Function
Is there any reason why this wouldn't work in Excel 2007 despite working fine in 2003?

.Range("C2:C15132") = Right(.Range("C2:C15132"), 7)

View Replies!   View Related
Worksheet Function In VBA
My workbook has a sheet called "MyList", another called "HisList" , a named range called "myNamedRange" which is "MyList!A:A".

How do I get this functionality of this excel formula

View Replies!   View Related
Replace Function In VBA
I'm working with a word table pasted into excel. The dashes in the word table paste into Excel (2003) as small boxes. I can use the find and replace function in Excel and in the Excel environment the replace function works. I recorded the replace function so I could develop the action in VBA. The recoded action does not work. The data I've pasted in below looks fine, however when pasted into Excel all of the "-" paste in as small boxes. When I used the chr function the chr code equals 63 which is "?". I used chr(63) in the replace function. This causes the VBA replace function to replace all characters in the file with dashes. I've tried several other codes (127,129,141,143,144,157) (none worked) based on the following website:

[url]

Cells.Replace What:=Chr(63), Replacement:="-" 'finds and replaces the - that converted to boxes

I can paste in the data using paste special "unicode text" but I loose the strike through formating which I need to identify obsolete data.

View Replies!   View Related
Vba Function Not Updating
I have the folowing vba function that i use to check the existance of files but when the workbook is refreshed, the formula doesn't update. It's only updated when i double click it's cell.

View Replies!   View Related
Using The OFFSET Function In VBA
I am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:

View Replies!   View Related
Use The Iferror Function In VBA
I'm trying to use the iferror function in VBA and the whole code is not executing, as the syntax error is being noticed first, and not allowing the whole code to execute. What I want to happen is if the vlookup returns an error, the cell value should be "*". The code is:

View Replies!   View Related
Find Function In Vba
I have this source data that has different types of currencies in it. It will be processed by a marco. But Before i do the processing, i would like excel to check if all currencies in the source data have had their rates determined by the user in another worksheet. This is the code i am using. However, when the marco trys to find rates that has not been determined (cannot be found in remarks sheets), it will just skip the msgbox code.

Find = Cells.Find(What:=local_currency, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

If Find = True And ActiveCell.Next.Next.Value <> "" Then
Find = Empty
Else
MsgBox "The currency " & local_currency & " does not exist in the currency list"
Sheets("Remarks").Select
End If

View Replies!   View Related
Maxif Function In Vba
I want to write a reuseable maxif function in vba. The following code is working when entered directly into the cell:

= SUMPRODUCT(MAX((B1:B6=B2) * (A1:A6)))

Now I want to pack this function into vba code to make it commonly available. I tried it with the following code but it doesn't work. I assume there is a problem with the passing of the range parameters. I also tried it with String and Variant as parameter types - without success. Can anybody help me?

Public Function MaxIF(criteriaRange As Range, searchValue As Variant, calcRange As Range)

AciveCell.Formula = "=SumProduct(Max((criteriaRange = searchValue) * (calcRange)))"

End Function

View Replies!   View Related
Countif Function In Vba
I'm trying to use the worksheet function countif in my code and it does not recognize it. But it does recognize counta. Here's a sample of my code.

Private Sub UserForm_Activate()
Dim MyUnion As Range
Set MyUnion = Union(Rows(12), Rows(14), Rows(16), Rows(18), Rows(20), Rows(22), _
Rows(29), Rows(31), Rows(33), Rows(35), Rows(37), Rows(39), _
Rows(46), Rows(48), Rows(50), Rows(52), Rows(54), Rows(56), _
Rows(63), Rows(65), Rows(67), Rows(69), Rows(71), Rows(73))
txtTtlDays.Text = Application.worksheetfunction.countif(MyUnion, "&A")
End Sub

View Replies!   View Related
Xirr Function In VBA
I am having trouble with the Xirr function in VBA. The problem revolves around the sensitivity of the solution to the 'initial guess'. specifically the boundary between a +ve and -ve guess. i.e. if the xirr is large and -ve a positive guess will always give me a solution very close to zero, as opposed to the more meaningfull solution say -65%.

To get around this i have constructed the UDF below, which is used as part of a sub, however for some investments i get the type mismatch error (error(13)) even though the arrays are of identical size and the data should be uniform as it is a dump from a database.

Has anyone encountered this 'type mismatch' problem with xirr before? is there a way to access the vba code for xirr so that it can be altered?

Function fkxirr(arrVals, arrDates)

Dim r As Double
Dim Step As Double
Dim tolerance As Double
Dim xir As Double

r = 10
tolerance = 0.000001
Step = 0.1

View Replies!   View Related
Workday Function Use In VBA
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$2-1,V2,Z2:Z$11)

P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.

View Replies!   View Related
Use Function/Formula Not Available In VBA
I want to evaluate Bessel functions as parts of a function in VBA. These functions are available in Excel not VBA. I tried to use range (). value to do this, i.e.

Function Bessel2(x)
Worksheets("Sheet2").Range("C1").Value = x
Bessel2 = Worksheets("Sheet2").Range("C2").Value
End Function

In cell C2, I enter the formula BESSELI(2,C1). But this did not work.

View Replies!   View Related
SUMIF Function In VBA Macro
I have a macro[A] that sums a column based on parameters passed to it from another macro[b]

Macro A

Sub SumColumns(varASheetName As Variant, varFormulaCell As Variant, varSumRange As Variant)

Sheets(varASheetName). Range(varFormulaCell).Value = Application.WorksheetFunction.Sum(varSumRange)

End Sub

' Macro B

Sub SumDebtorsStuff()
Call SumColumns("DebtorsRaw", "D10", Range("D21", Range("D65536").End(xlUp)))
End Sub ...........



View Replies!   View Related
Calling A Custom Function Within VBA
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 Replies!   View Related
Writing VBA Macro Or Function
I have Asset Class in Column I and if it is 364A-EL or 365A-EL or 368A-EL or 368B-EL or 369A-EL or 371A-EL or 371B-EL or 373A-EL or 397C-EL then 'POLES' assigned in to Owner Class (Column J).

If Asset Class is 366A-EL or 367A-EL or 367B-EL or 368C-EL or 369B-EL or 371C-EL or 373B-EL then assign UGCBL to Owner Class.

Should we write the macro or can it be accomplished by any Excel Function?

View Replies!   View Related
Using Rank Worsheet Function In VBA
I am trying to rank number that do not have a specific adress. My problem is that I do not know how to define all parameters of this function

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved