Worksheet MOD formula in VBA

Lectricman

New Member
Joined
Feb 18, 2016
Messages
12
I hope someone can help me with this as it's driving me nuts.
iRow 3 and 4 are time values that may span more than 1 day.
For some reason the line of code in red, puts FALSE in the worksheet cell.
I have posted the whole procedure so you can see what is going on.

Code:
Private Sub btnUpdate_Click()
 Dim iRow As Long
 Dim WS As Worksheet
 Dim sheet As String
 sheet = ComboBox1.Value
 Set WS = Worksheets(sheet)
 With WS
       iRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
        SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        .Cells(iRow, 1).Value = Me.txtWhen.Value
        .Cells(iRow, 3).Value = Me.txtIn.Value
        .Cells(iRow, 4).Value = Me.txtOut.Value
        .Cells(iRow, 2).Value = Me.TextBox1.Value
[COLOR=#ff0000]        .Cells(iRow, 5).Value = (Formula = "=MOD(iRow, 4-iRow, 3,1)")[/COLOR]
    End With
    With UserForm1
    .txtIn.Value = ""
    .txtOut.Value = ""
    .ComboBox1.Value = ""
    End With
    End Sub
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,629
Office Version
2010
Platform
Windows
For some reason the line of code in red, puts FALSE in the worksheet cell.
[....]
.Cells(iRow, 5).Value = (Formula = "=MOD(iRow, 4-iRow, 3,1)")
I don't know why you would ever think that works. The VBA Format function is comparable to the Excel TEXT function. It simply formats (klunk!), and the second parameter is a format specification (albeit different from TEXT format specifications in some respects).

It is also not clear to me how you want to use a "mod" operation. The MOD syntax that you use is nothing like Excel MOD usage.

I wonder if this is what you want:

.Cells(iRow, 5).Value = Mid(.Cells(iRow, 4-iRow), 3,1)

That extracts one character from Cells(iRow,4-iRow) at character position 3.

If you truly want a MOD operation, the Excel syntax is MOD(x,y).

I have never understood why there is no WorksheetFunction.Mod method. But the following does the same thing in VBA:

x - y*Int(x/y)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
I have never understood why there is no WorksheetFunction.Mod method
VBA has its own Mod function, like
Code:
Range("F4") = Range("D4") Mod Range("C4")
Where F4 will be the remainder after dividing D4 by C4.

Lectricman
Can you please explain what you are trying to get with that formula?
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,646
Office Version
2010
Platform
Windows
VBA has its own Mod function, like
Code:
Range("F4") = Range("D4") Mod Range("C4")
Where F4 will be the remainder after dividing D4 by C4.
VBA's Mod operator (not function) differs significantly from Excels MOD function is a few respects. First, it handles negative values differently... second, if you use 1 as the divisor, it will return 0, not the decimal portion of the floating point number... third, it handles floating point number differently (it uses Banker's Rounding to round all floating point number to whole numbers before performing it operation on those numbers whereas Excel doesn't).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,512
Office Version
365
Platform
Windows
VBA's Mod operator (not function) differs significantly from Excels MOD function is a few respects. First, it handles negative values differently... second, if you use 1 as the divisor, it will return 0, not the decimal portion of the floating point number... third, it handles floating point number differently (it uses Banker's Rounding to round all floating point number to whole numbers before performing it operation on those numbers whereas Excel doesn't).
Cheers for that Rick.
Whilst it's not something I use very much, I'll try to remember it for the future.
 

Lectricman

New Member
Joined
Feb 18, 2016
Messages
12
Thank you for the replies. What I am trying to achieve is as follows:
Column 3 holds clocking in time, column 4 holds clocking out time, which may go beyond midnight.
The formula "MOD(D1-C1,1)" gives me the actual time between clocking in and out.
The red formatting in the original post was merely to highlight the line of code I was having trouble with.
So if a member of staff starts work at 14:00 and leaves at 01:00 the next day the mod function gives me the number of hours they spent at work.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,459
Office Version
365
Platform
Windows
This is how you put a formula in a cell.
Code:
 .Cells(iRow, 5).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"
 

Forum statistics

Threads
1,089,215
Messages
5,406,890
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top