Worksheetfunction.countif for excel 2010

[diamantebonita]

<TABLE style="WIDTH: 528pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=705><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><COL style="WIDTH: 209pt; mso-width-source: userset; mso-width-alt: 10203" width=279><COL style="WIDTH: 143pt; mso-width-source: userset; mso-width-alt: 6985" width=191><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 81pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=108>DATE ENTERED</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #fabf8f; WIDTH: 209pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl74 width=279>VENDOR</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fabf8f; WIDTH: 143pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=191>AMOUNT</TD><TD style="BORDER-BOTTOM: windowtext 2pt double; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fabf8f; WIDTH: 95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=127>DUE DATE</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73 height=21>6/10/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl75>con edison</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl67>60.00</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl70>7/1/2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=21> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl79> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl71> </TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: #fabf8f 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl72 height=25>6/22/2011</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl76>kleenex</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl77>87.39</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl78>6/30/2011</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: #fabf8f; BORDER-RIGHT: #f0f0f0" class=xl72 height=25>6/22/2011</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl76>staples</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl77>99.00</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl78>6/30/2011</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: #fabf8f; BORDER-RIGHT: #f0f0f0" class=xl72 height=25> </TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl76> </TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl77> </TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl78> </TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: #fabf8f; BORDER-RIGHT: #f0f0f0" class=xl72 height=25>6/21/2011</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl76>texas instruments</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl77>56.00</TD><TD style="BORDER-BOTTOM: #fabf8f 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #fabf8f; BORDER-RIGHT: windowtext 1pt solid" class=xl78>6/29/2011</TD></TR></TBODY></TABLE>

the above is just a sample of a much longer list. But the list itself is broken up into several named ranges. Within each range i want to count the number of entries where the date is today or a week from today (date + 7). I'm doing this in VBA so I'm using worksheetfunction.countif(). But I cant seem to get the parameters right.
When i type:

worksheetfunction.countif(llci,">=date + 7")

the error says "Compile error, expected: ="
Can someone tell me what im doing wrong?

llci is just the name of my first range.

 

[p45cal]

try (untested):
worksheetfunction.countif(llci,">=" & date + 7)

although date + 7 will count how many there are AFTER a week from today - but that's may what you want to do (to subtract it from >=today's date).

 

[diamantebonita]

when i typed in the formula it gave me a syntax error.

 

[p45cal]

when i typed in the formula it gave me a syntax error.

So at least the whole line from the code would be useful, if not a bit more for context.

 

[Marcelo Branco]

Maybe


Application.CountIf(Range("llci"), ">=" & Format(Date + 7, "mm/dd/yyyy"))


(i'm using Format because of possible wrong results depending on dates regional settings)


HTH


M.

 

[diamantebonita]

this section copies certain cells to another part of the spreadsheet, making a list
For Each icell In ws1.Range("G2:G" & lastrow)
If icell.Value >= Date + 7 And icell.Value < Date + 14 Then
Range(icell.Offset(0, -2), icell).Copy Destination:=ws1.Range("AA" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next icell

Range("AA2").Select

listrow = ws1.Range("AC" & Rows.Count).End(xlUp).Row

counti = worksheetfunction.countif(llci, ">=date = 7)

this section will use the count to skip down a number of rows, insert a row and add text (not quite finished with this section)
If counti > 0 Then
Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

 

[diamantebonita]

I get a syntax error when I entered the code in Marcelo....

 

[diamantebonita]

So i was thinking of ditching countif as a solution and doing this instead:

For Each g In ws1.Range(llci)
If g.Value >= Date + 7 And g.Value < Date + 14 Then
counti = counti + 1
End If
Next g

what you think?

 

[Marcelo Branco]

I get a syntax error when I entered the code in Marcelo....

It worked perfect for me

Sub test2()
    MsgBox Application.CountIf(Range("llci"), ">=" & Format(Date + 7, "mm/dd/yyyy"))
End Sub

M.

 

[p45cal]

this section copies certain cells to another part of the spreadsheet, making a list
For Each icell In ws1.Range("G2:G" & lastrow)
If icell.Value >= Date + 7 And icell.Value < Date + 14 Then
Range(icell.Offset(0, -2), icell).Copy Destination:=ws1.Range("AA" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next icell

Range("AA2").Select

listrow = ws1.Range("AC" & Rows.Count).End(xlUp).Row

counti = worksheetfunction.countif(llci, ">=date = 7)

this section will use the count to skip down a number of rows, insert a row and add text (not quite finished with this section)
If counti > 0 Then
Rows("1:1").EntireRow.Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

This line from above,
counti = worksheetfunction.countif(llci, ">=date = 7)
isn't the same as any suggestions put forward so yes, it will give a syntax error.