VBA Adding number to cell.value type mismatch

[scoha]

Why do I get a TYpe Mismatch error for this line:

If Cell.Value + Range("Duration").Value < Range("Now").Value And Cell.Value > 0 Then

when cell.Value is a date and "Duration" has a number in it and "Now" has a date??

 

[Rick Rothstein]

There is no way for us to tell without seeing the rest of your code; however, you should be able to trace the problem down quite quickly... simply print out each item in the immediate window to see what is actually in each component. That is, execute each of these statements to see which one or ones error out or else display something that is not what you think it should be ...

? Cell.Value

? Range("Duration").Value

? Range("Now").Value

 

[scoha]

Thanks Rick,will do as suggested with Intermediate window, but in the meantime here is my code:

Option Explicit
Private Sub Worksheet_Activate()

Dim Rng As Range
Dim Area As Range
Dim Cell As Range


Sheets("Staff").Range("RangeNames451").Copy Range("b9")
Sheets("Staff").Range("RangeNames454").Copy Range("b101")

Set Rng = Range("WI451Data", "WI454Data")
    
    For Each Area In Rng.Areas
       For Each Cell In Area
            Cell.Interior.ColorIndex = 0
        Next Cell
    Next Area

    For Each Area In Rng.Areas
       For Each Cell In Area
            If (Cell.Value + Range("Duration").Value) > Range("Now).value And Cell.Value + Range("Duration").value < Range("OneMonth").Value Then
            Cell.Interior.ColorIndex = 44
            
            Else
            If Cell.Value + Range("Duration").Value < Range("Now").Value And Cell.Value > 0 Then
            Cell.Interior.ColorIndex = 38
            
            End If
            End If
       
        Next Cell
    Next Area




End Sub

 

[Ruddles]

Or insert this line immediately before the statement which raises the error:-

Debug.Print Cell.Value, Range("Duration").Value, Range("Now").Value

(which is basically the same as Rick said).

The output appears in the Immediate window (Ctrl-G).

 

[Rick Rothstein]

Or insert this line immediately before the statement which raises the error:-

Debug.Print Cell.Value, Range("Duration").Value, Range("Now").Value

I'm not sure that will work... won't the whole line not print anything if one of the print items is an error? (Can't test it now, I'm going to sleep for the night.)

 

[Ruddles]

Ah yes, of course. In that case:-

Debug.Print Cell.Value, : Debug.Print Range("Duration").Value, : Debug.Print Range("Now").Value

 

[Norie]

Try enclosing the 2 expressions either side of the And in parentheses.

(expr1) And (expr2)