VBA - Type Mismatch error in date subtraction macro

[dtrynoski]

I don't know why I'm getting a Type Mismatch error. I'm trying to do a simple subtraction macro with a variable row spreadsheet and getting a Type Mismatch error on this line of code: I'm subtracting two dates, column I which contains expiration date must cell "AA1" which contains today's date.

Range("M" & i).Value = Range("I" & i).Value - Range("AA1").Value


I'm subtracting two dates, column I which contains expiration date must cell "AA1" which contains today's date.

My macro is as follows:

'exception age calculation
Range("M1").Select
ActiveCell.FormulaR1C1 = "Days to Expiration"
Range("AA1").Value = Date

With Worksheets("Archer Search Report")
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
Range("M" & i).Value = Range("I" & i).Value - Range("AA1").Value
Next i
End With

 

[Jonmo1]

When using a With Structure, you need to proceed objects you want associated with that With, with a period..
I'm guessing the Range("AA1") that you put Date in is NOT in that With reference, but the other 2 ranges are.

Try

With Worksheets("Archer Search Report")
     FinalRow = .Cells(Rows.Count, 1).End(xlUp).Row
     For i = 2 To FinalRow
         .Range("M" & i).Value = .Range("I" & i).Value - Range("AA1").Value
     Next i
End With

 

[dtrynoski]

I still get the same error with the change. Do I need to change the type or format on one of the cells?

 

[Jonmo1]

Ok, which sheet are each of the 3 cells on?

Range("M" & i) is on sheet ??
Range("I" & i) is on sheet ??
Range("AA1") is on sheet ??

 

[dtrynoski]

all on sheet "Archer Search Report"

 

[Jonmo1]

Try

With Worksheets("Archer Search Report")
    .Range("M1").Value = "Days to Expiration"
    .Range("AA1").Value = Date
    FinalRow = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow
        .Range("M" & i).Value = .Range("I" & i).Value - .Range("AA1").Value
     Next i
End With

 

[dtrynoski]

I still got a type mismatch error. I did some digging on formatting and it worked after I added the following code. I suspected it had something to do with my column I format which this appears to correct:

Dim r As Range
Dim setdate As Range
Set setdate = Range(Cells(2, 9), Cells(2, 9).End(xlDown))
With setdate
.NumberFormat = "dd.mm.yyyy" 'I have the data in format "dd.mm.yy"
.Value = .Value
End With

 

[Norie]

It sounds like you have/had some text dates in the column,