Type Mismatch error adding 10 Seconds to the previous cell's value

Question

I would like to write to a cell the previous cells time value + 10 seconds.

I have tried several approaches after a lot of googling, however below is what I started with and what I would like to understand is why this doesn't work - as in my head it is logical.

The cell data is in the special format DD:MM:YYYY HH:MM:SS - which is a reason this may not work, however if I add + (10 / (3600 * 24)) to the cell manually then it successfully adds on 10 seconds.

Dates are stored as custom and show up as 24/09/2018 08:41:09.

Public Sub Add_Row()
    Dim Row As Variant
    Dim LR As Long
    Dim x As Integer
    Dim y As Integer

    LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row 'Counts number of rows
    x = 1
    Row = 1

    Do Until x = LR
        If Cells(Row, 2).Value <= 1 Then 'If the value of the cell is less than or equal to one do nothing and increment
            Row = Row + 1
            x = x + 1
        Else
            y = Cells(Row, 2).Value - 1 'Need a variable for the number of rows we require based on how many missed points we have

            For k = 1 To y
                ActiveSheet.Rows(Row).Insert Shift:=xlDown
                Cells(Row, 1).Value = Cells(Row - 1, 1).Value + (10 / (3600 * 24))
            Next

            Row = Row + y + 1
            x = x + 1
        End If
    Loop
End Sub

Could you confirm the data are stored as date with a special format, and not as text? — Vincent G, Jan 14 '19 at 16:19
Hi Mathieu, by doesn't work I mean it says there is a type mismatch. I shall update my post with the full code if that helps :) — FormalAbility, Jan 14 '19 at 16:20
Also check this post: https://stackoverflow.com/questions/17359835/what-is-the-difference-between-text-value-and-value2 — Vincent G, Jan 14 '19 at 16:21
Dates can't show up as `DD/MM/YYYY` with a number format that says `DD:MM:YYYY`, for one. Why is `Row` declared `As Variant` if it's supposed to be a row number? These counters are making my head spin... A type mismatch looks like you're trying to add a `Double` to a `String`. What does `Debug.Print TypeName(Cells(Row, 1).Value)` output? `Date`? Or `String`? — Mathieu Guindon, Jan 14 '19 at 16:28
Put a breakpoint (F9) on the line that blows up, then inspect the value of `Row`, then the type of `ActiveSheet.Cells(Row, 1)`. If that's not a `Date`, that's why you're getting a type mismatch. If `Row` is `1` and there's a column heading in that cell, there's an off-by-one error in your logic and you need to skip the header row. — Mathieu Guindon, Jan 14 '19 at 16:29

score 0 · Answer 1 · answered Jan 15 '19 at 11:11

MUltiplying 3600 by 24 in VBA will give an overflow error, because the max value of a 16 bit integer (the default type for the result when both the input numbers are integers) is 32767. You can either use a "#" which will tell VBA that you want to treat the result as a double, like so:

Cells(Row, 1).Value = Cells(Row - 1, 1).Value + (10 / (3600# * 24))

OR you can use "#12:00:10 AM#" which represents 10 seconds, rather than attempting to calculate it, like so:

Cells(Row, 1).Value = Cells(Row - 1, 1).Value + #12:00:10 AM#

Hope this helps.

Type Mismatch error adding 10 Seconds to the previous cell's value

1 Answers1