Worksheet MOD formula in VBA

[Lectricman]

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.

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

 

[joeu2004]

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]

I have never understood why there is no WorksheetFunction.Mod method

VBA has its own Mod function, like

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?

 

[Rick Rothstein]

VBA has its own Mod function, like

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]

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]

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]

This is how you put a formula in a cell.

 .Cells(iRow, 5).FormulaR1C1 = "=MOD(RC[-1]-RC[-2],1)"

 

[Lectricman]

Brilliant Norie, works like a charm. Thank you very much.