Date problem

[gibbo1715]

Hi All

What im trying to do is use the code below to put a sheet name into a list box if the date in it is between now - 30 and now +7 but it seem to add any date greater than today, any ideas please?

I should add this code is part of a larger case statement

For Each ws In ActiveWorkbook.Worksheets
        If ws.Range("C10").Value >= Date - 30 And Range("C10").Value <= Date + 7 Then
        ListBox1.AddItem ws.Name
        End If
Next

[mvidas]

Gibbo,

Put the ws. qualifier before the second Range statement, like

If ws.Range("C10").Value >= Date - 30 And ws.Range("C10").Value <= Date + 7 Then

The way you have it, it is checking to see if the active sheet's C10 is less than date + 7. Should take care of it for you!

Matt

[gibbo1715]

Cant believe i missed that but there you go, sometimes its the simple stuff that gets you

Thanks for opening my eyes matt

[Killian]

Doesn't sound like it's resolving the date addition/subtraction. You can force it by using the DateAdd function

If ws.Range("C10").Value >= DateAdd("d", -30, Date) And _
Range("C10").Value <= DateAdd("d", 7, Date) Then

Ahh yes, well spotted