Type Mismatch

[hallingh]

I'm running an If/And statement and getting a Type Mismatch error. The If statement is checking to see if the cell (which is actually 8 cells in column Y merged together) is blank and if it is more than 30 days before today's date. I am also looping this, so I'm using a variable, Q, to reference the row number. Here is the code:

If ActiveSheet.Range("Y" & (Q - 1) & ":Y" & (Q + 6)) <> "" And (Date - Range("Y" & (Q - 1) & ":Y" & (Q + 6)).Value) > 30 Then

I tried just referenceing the merged cell by using (Q-1) alone, which is its highest cell, but that gives me the same error. It's the end of the day, and I'm sure I'm missing something obvious, but I can't seem to figure this out!

Any help you can give me is greatly appreciated. Thanks!

Hank

 

[mouse88]

Can you post all of the code or the workbook.

Is "Date " a variable and if so is it formatted as a date.

What is the data in the cell formatted as?

 

[mouse88]

Here ya go:

Public Sub TestSub()
If Not IsDate(ActiveSheet.Range("Y1").Value) Then
    MsgBox ("Invalid Date")
ElseIf ActiveSheet.Range("Y1").Value < Now() - 30 Then
    MsgBox ("More Than 30 Days Old")
End If
End Sub

The data in the cell should be formatted as a date.

Matt

 

[hallingh]

Date is the way to use today's date in vba. As far as I know it does not have to be defined? Am I wrong on this? And the cells in column y are formatted as a date. I Dim Q As Long.

This code was working when it was written like this:

If ActiveSheet.Range("T" & Q) <> "" And (Date - Range("T" & Q).Value) > 30 Then

with the cell in T not being merged, but a complication has forced me to use the date in the merged cell of column y instead of the date in column T. Both are formatted exactly the same.

Thanks a ton for the response and the look.

Hank

 

[hallingh]

I'm getting an Else without If error now? Not really sure why, though, as the statement looks fine to me...

 

[mouse88]

I amended the code check it again.

Matt

 

[hallingh]

Aha! Those changes made it work great. Maybe it was comparing it to Now() instead of Date??? Either way, thanks a ton for the code, extremely helpful!

Hank