Excel-VBA: Run-time error when comparing dates

Question

I've been stumped on this problem for a few days now: a

"Run-time error '13': Type mismatch"

when I'm trying to compare dates. I have looked my problem up to see if anyone else has run into the same issues, and found this. I have tried implementing the solution to that question with no luck.

I'm working with a userform called calendar that holds dates in labels overlayed on correlating textboxes that the user can edit, and place notes into as shown here.

I save the notes to column B in the worksheet and the date of the note in column A.

Right now, I'm trying to pull the notes from the worksheet and place them into the textboxes by comparing dates and this is where I'm having problems. When I compare the dates I pull the label caption and save it as date curDate, and pull the worksheet date and save it as wsDate.I initially had a message box in the if statement (now commented out) to see if the code was running ... and it is, sort of. It will run until the first match and then give the run-time error.

Dim ws As Worksheet

Dim lrow As Integer 'last row
Dim row As Integer 'used for looping through rows

Dim wsDate As Date 'worksheet date
Dim curDate As Date 'label date

Dim i As Integer

Set ws = Sheets("Hidden Information")

lrow = ws.Range("A" & ws.Rows.Count).End(xlUp).row

With ws 'Hidden Information worksheet

    For i = 1 To 38 'number of labels

        For row = 2 To lrow 'runs til the last row on the worksheet

            wsDate = DateSerial(Year(.Cells(row, 1).Value), _
                Month(.Cells(row, 1).Value), Day(.Cells(row, 1).Value))

            curDate = DateSerial(Year(Controls("Label" & i).Caption), _
                Month(Controls("Label" & i).Caption), Day(Controls("Label" & i).Caption))

            If wsDate = curDate Then Controls("TextBox" & i).Text = vbNewLine & _
                vbNewLine & .Cells(row, 2).Value 'MsgBox "it's a match!"

        Next row

    Next i

End With

I've reworked the code a few times, and tried saving the dates in different ways such as,

        temp = CDate(.Cells(row, 1).Value)
        wsDate = Format(temp, "mm/dd/yy")

        temp = CDate(Controls("Label" & i).Caption)
        curDate = Format(temp, "mm/dd/yy")

But it has the same problem: would run 'til the first match and then give the runtime error.

I have a feeling the solution is something obvious that I can't see because I've been staring at this for a few days now...

If anything needs to be clarified please let me know.

Write `MsgBox curDate` and `MsgBox wsDate` on the two lines before the error. What happens? — Vityata, Jul 31 '18 at 13:36
Note: Don't use `Integer` for row counting variables. Excel has more rows than `Integer` can handle. It is recommended [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA as there is no benefit in `Integer` at all. — Pᴇʜ, Jul 31 '18 at 13:38
Check your cells are having date value. All your functions `Year()`, `Month()`, `Day()` expects a date input. Try checking the cells values using `IsDate()` function. — nagarajannd, Jul 31 '18 at 13:39
@Vityata - It's looping okay - showing all the right dates and such, but as soon as 'curDate' = wsDate' it gives the error. — court_k, Jul 31 '18 at 13:42
@Pᴇʜ - I know, but this workbook won't be used for very long! So I don't expect the user to create enough notes (or rows) to require declearing row as 'Long' but I will make the changes anyhow. — court_k, Jul 31 '18 at 13:44
@nagarajannd I just checked if 'wsDate' and 'curDate' are dates using isDate (per your suggestion) and they are both dates. — court_k, Jul 31 '18 at 13:50
@court_k put `If wsDate = curDate Then` part into a single line and close it after your command line with a `End If` then re-test in which line the error occurs. — Pᴇʜ, Jul 31 '18 at 13:51
@court_k the if-statement, where you catch the first match... is the error part of the argument (*If wsDate = curDate*) or part of the true case (*Then Controls(...*)? You should have the text flagged in VBA when the error occurs. I just want to verify what we're looking to fix, if the dates are an issue, or how you are writing to textbox is an issue. I originally had a comment in here asking if you tried .Value instead of .Text for the true-case, but deleted it because I wanted to know what we were really dealing with. — Cyril, Jul 31 '18 at 13:57
@Cyril, oh okay! I'm not quite sure how to test for that so what I did was if 'wsDate = curDate' then it would change the colour of the cell. This had the same result as the message box test I did. It changes the colour of the cell of the first match then gives an error. Could you maybe provide a better way for testing this, if you don't mind? :) — court_k, Jul 31 '18 at 14:02
@court_k Default VBA editor (via alt+F11 from Excel) will highlight the line of code Yellow when you Debug after an error message. You would be able to see which part of the if-statement is flagged as yellow. If the whole line is being flagged (it shouldn't, but if), then you can fully write out yoru if statement such that line 1 is *if xxx then*, line 2 is the true-case, and line 3 is *End If*, which should make it very clear. — Cyril, Jul 31 '18 at 14:10
@Cyril, oh the debugger! Yep, i'll use that and let you know what happens. — court_k, Jul 31 '18 at 14:27
@DavidZemens it's a note associated with the day (i.e. 07/31/2018 | Meeting with Tom). — court_k, Jul 31 '18 at 14:27
Question, are you writing the data from ColB to the textbox, or are you moving the textbox data to a cell in ColB? If you are moving the textbox data to the specific cell in ColB then your last line is incorrect. — GMalc, Jul 31 '18 at 14:34
@Cyril, thank you so much! I figured out my problem. The program would break at `curDate = ... `. This is because some labels are empty. So I made the wrong assumption! I loop through ALL the labels, but not all of them contain dates! Now I have implemented a loop to check if the label is vbNull and then if it is a break out of that loop, save the counter "length" (minus 1) and use that for my upper bound. — court_k, Jul 31 '18 at 14:34
@court_k Interpreting the debugger... the best part of coding, minus when something works on the first try. Please either make an answer to yourself stating the issue, so that it can be closed as to not have this question linger open, or I can make one for you... self answers usually take some time before they can be accepted. — Cyril, Jul 31 '18 at 14:36
@Cyril if you don't mind could you make one? Thank you in advance! :) And thanks for all the help!! I really appreciate it!! :) — court_k, Jul 31 '18 at 14:44

score 0 · Answer 1 · answered Jul 31 '18 at 14:25

This ought to help you debug. I suspect there's some problem with the .Cells(row,2) value. So let's isolate that and make sure we're casting to string. This may still raise an error if for instance that cell contains an error value, but at least this should help identify the problem:

Dim thisCaption as String, newText as String
For i = 1 To 38 'number of labels

    For row = 2 To lrow 'runs til the last row on the worksheet

        wsDate = DateSerial(Year(.Cells(row, 1).Value), _
            Month(.Cells(row, 1).Value), Day(.Cells(row, 1).Value))
        thisCaption = Controls("Label" & i).Caption
        curDate = DateSerial(Year(thisCaption), Month(thisCaption), Day(thisCaption))

        If wsDate = curDate Then 
            newText = vbNewLine & vbNewLine & CStr(.Cells(row, 2).Value)
            Controls("TextBox" & i).Text = newText
        End If
    Next row

Next i

NB: I think but have not tested that your calls to DateSerial are redundant, and could probably be removed, instead using:

wsDate = CDate(.Cells(row, 1).Value)
curDate = CDate(thisCaption)

I believe I have figured out my problem, but thank you for the help! :) I will implement this because I like how it explicitly shows the type instead of sorta, *shrug* — court_k, Jul 31 '18 at 14:41

score 0 · Accepted Answer · answered Jul 31 '18 at 14:57

Ensure that you're using the debugger to check for where the error is occurring.

From the comments, your error was at currDate = ..., which you appear to have fixed by correcting the range in which the code is iterating.

This comment exists to close-out the Question, which was answered in the comments section, as to not let it linger.

score -1 · Answer 3 · answered Jul 31 '18 at 13:49

-1

I'm not quite caught up with the way you reference "worksheet.Cells(row, col).Value" cause I always use .formula or .text property of the Cell. But suppose .Value property gives numeric value, then assign it directly to .Text which I guess expects String type might be the cause of this error?

answered Jul 31 '18 at 13:49

Hongleigiy

11
4

1

No, numbers get automatically/silently cast into strings. – Pᴇʜ Jul 31 '18 at 13:50
According to [this](https://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/) says it maintains it's format unless I'm misunderstanding something. – court_k Jul 31 '18 at 13:56

Excel-VBA: Run-time error when comparing dates

3 Answers3