Run Time Error '13: Type Mismatch when comparing Dates - EXCEL VBA

Question

I keep getting a "Run Time Error '13': Type Mismatch" error when comparing 2 dates. The code grabs a date from a second Workbook, which in this case I've tried to paste it into a cell to make sure it's a date... Which it is. Then it tries to compare it to a date already on the current Workbook. The pasted date and the other dates are identical formats. I have no idea why it can't compare the 2 dates! I've also tried putting CDate() around each of the components to no avail. Please help.

Sub NewMacro()
Dim CurrentWB As Workbook
Dim ForecastWB As Workbook
Dim strDate As Date

Set CurrentWB = ActiveWorkbook

Application.DisplayAlerts = False
Set ForecastWB = Workbooks.Open("My Other Workbook File Name")
Application.DisplayAlerts = True
strDate = ActiveWorkbook.Worksheets("My Sheet Name").Cells(20, "N").Value

ThisWorkbook.Activate

If Cells(5, 5) = Range("A:A") Then 'TYPE MISMATCH HERE
    Set x = Range("A:A").Find(what:=Cells(5, 5), lookat:=xlWhole)
    Cells(x, 5) = strDate
End If

End Sub

please check for both dates that `IsDate()` is true... there may be the chance that at least one date is treated like a string... please see [THIS](http://stackoverflow.com/questions/10502802/cdate-type-mismatch-error) — Dirk Reichel, Aug 05 '16 at 12:19
Wouldn't `Cells(5, 5) = Range("A:A")` be comparing a single cell to a whole column? `Cells(5, 5) = Range("A1")` works. — Darren Bartrup-Cook, Aug 05 '16 at 12:27
@DarrenBartrup-Cook Yes! I'm trying to find the Date in a Range of cells! Is this not the way to do it? — dReDone, Aug 05 '16 at 12:39

score 3 · Accepted Answer · answered Aug 05 '16 at 12:38

From what I can read in your code: Cells(5, 5) = Range("A:A") - you can't compare a single value to a whole column of values.

Cells(x, 5) = strDate - x is a range object, not a row number. Use x.Row instead.

If the value in Cells(5,5) isn't found then x will equal nothing causing an error on this line: Cells(x, 5) = strDate

Try adapting this code:

Sub Test()

    Dim x As Range

    With ThisWorkbook.Worksheets("Sheet1")
        Set x = .Range("A:A").Find(What:=.Cells(5, 5), LookIn:=xlValues, LookAt:=xlWhole)
        If Not x Is Nothing Then
            .Cells(x.Row, 5) = CDate("5 Aug 2016")
        End If
    End With

End Sub

It will search column A in Sheet1 for the value in cell Sheet1!E5. It will then put 05/08/2016 in column five of the row it finds.

With...End With: https://msdn.microsoft.com/en-us/library/wc500chb.aspx

Thanks so much Darren! This works perfect. I very much appreciate it! — dReDone, Aug 05 '16 at 13:04

Run Time Error '13: Type Mismatch when comparing Dates - EXCEL VBA

1 Answers1