A question about Counting in VBA
 

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

--
Message posted from http://www.ExcelForum.com

A question about Counting in VBA
 

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

--
Message posted from http://www.ExcelForum.com

A question about Counting in VBA
 

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

--
Regards,
Tom Ogilvy

"abxy " wrote in message
...
Ok, i'm having a little trouble with this, can i get some help?

The following basically says that for every sheet in the workbook
(whose name in the current month), count how many cells in range H1 to
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 same
thing, except this time count the cell ONLY if the cell has today's AND
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 know
how to do a countif with only one condition. but my problem is that i
need a countif done with conditions for the count this time.

any help would be GREATLY appreciated, thanks much!


---
Message posted from http://www.ExcelForum.com/