A question about Counting in VBA

[abxy]

Ok, i'm having a little trouble with this, can i get some help?

The following basically says that for every sheet in the workboo
(whose name in the current month), count how many cells in range H1 t
H275 have today's date:

count= 0

For Each wks In Workbooks(Format(Date, "mmmm") & ".xls").Worksheets
count = count + Application.CountIf(wks.Range("H1:H275"), Date)
Next


...that works absolutely fine. Now, i'm trying to make it say the sam
thing, except this time count the cell ONLY if the cell has today's AN
the offset(0, -4) of the cell's value is "PICK UP".

I can't seem to be able to put in that 'and' part to it. I only kno
how to do a countif with only one condition. but my problem is that
need a countif done with conditions for the count this time.

any help would be GREATLY appreciated, thanks much

 

[abxy]

Great, there isn't a reply yet. Well folks, i solved my ow
problem...and here's how it's done:

For Each wks In Workbooks(Format(Date, "mmmm") & ".xls").Worksheets
For Each r In wks.Range("H1:H275")
If r.Value = Date And r.Offset(0, -4) = "PICK UP" Then
PU = PU + 1
End If
Next
Nex

 

[Tom Ogilvy]

Sub Countem()
Dim count as Long, rng as Range, rng1 as Range
Dim sForm as String
Count = 0

For Each wks In Workbooks(Format(Date, "mmmm") & ".xls").Worksheets
Set rng = wks.Range("H1:H275")
Set rng1 = rng.Offset(0, -4)
sform = "SumProduct(--(" & rng.Address(external:=True) & _
"=DateValue(""" & Format(Date, "mm/dd/yyyy") & """),--(" & _
rng1.Address(external:=True) & "=""Pick up""))"
Count = Count + Evaluate(sform)
Next


End Sub