Add Date condition to Macro

[Jameo]

Hi all,

I've been trying to add a date condition to the following macro, but not having much sucess.

Public Sub Breaches()
Dim sh      As Worksheet, _
    dSh     As Worksheet, _
    shLR    As Long, _
    i       As Long, _
    shd     As Long, _
    shl     As Long, _
    dRow    As Long
    
Application.ScreenUpdating = False
    
Set dSh = Sheets("Forcasting")
shd = 1
For Each sh In ActiveWorkbook.Worksheets
    dRow = 2
    Select Case sh.Name
        Case "DifferenceData", "Breaches", "StDev", "Breaches and Date", "Forcasting"
            'do nothing
        Case Else
            dSh.Cells(1, shd).Value = sh.Name
            shLR = sh.Range("A" & Rows.Count).End(xlUp).Row
            For i = 2 To shLR
                If sh.Cells(i, 7).Value = "Y" Then
                
                    sh.Cells(i, 9).Copy Destination:=dSh.Cells(dRow, shd)
                    dRow = dRow + 1
                End If
            Next i
            shd = shd + 1
            End Select
Next sh
Application.ScreenUpdating = True
End Sub

I would like the macro in the following line:

If sh.Cells(i, 7).Value = "Y" Then

to also check if the date in column 1 is in April. I've tried

If sh.Cells(i, 1).Value < 31 / 4 / 2011 And sh.Cells(i, 1).Value > 1 / 4 / 2011 And sh.Cells(i, 7).Value = "Y" Then

and replacing value with date, but no sucess.

Any ideas?

Thanks in advance guys

 

[Gerald Higgins]

I'm not a VBA expert, but if your date analysis code DID work, it would
a) have to deal with a non-existent date - only 30 days in April, not 31
b) treat any items with a date of 1st April as NOT being in April. You would need to use >= instead of >

Maybe the way forward is to use something like the worksheet MONTH() function, and determine whether this returns 4 or not.
Sorry, I don't know how to code this in VBA but I'm sure it will be possible.

 

[dcardno]

I think you have to enclose date constants in "#" signs - for example, I cut and pasted this out of my immediate window:

? date()
2011-05-18 
? date() = 2011-05-18
False
? date() = #2011-05-18#
True

As well, why not just test for:
If Month(sh.cells(i,1)) = 4 Then...

 

[Gerald Higgins]

As well, why not just test for:
If Month(sh.cells(i,1)) = 4 Then...

That sounds like what I had in mind . . .

 

[Jameo]

I think you have to enclose date constants in "#" signs - for example, I cut and pasted this out of my immediate window:

? date()
2011-05-18 
? date() = 2011-05-18
False
? date() = #2011-05-18#
True

As well, why not just test for:
If Month(sh.cells(i,1)) = 4 Then...

Thanks dcardno, sounds like the best way to go forward with this. I'll give that a go today.

Thanks for your input as well Gerald.

 

[Jameo]

Thanks guys,

Both,

If Month(sh.Cells(i, 1)) = 4 And sh.Cells(i, 7).Value = "Y" Then

and

If sh.Cells(i, 1).Value < #4/30/2011# And sh.Cells(i, 1).Value > #4/1/2011# And sh.Cells(i, 7).Value = "Y" Then

Work equally well. When the date is specified it obviously has to be in the American date format of mm/dd/yyyy

Cheers again