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 — Dirk Reichel
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
@DarrenBartrup-Cook Yes! I'm trying to find the Date in a Range of cells! Is this not the way to do it? — dReDone

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2016-08-05T12:38:46

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

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

1 Answers