Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting question | Excel Worksheet Functions | |||
Counting Question! | Excel Worksheet Functions | |||
Counting Question | Excel Worksheet Functions | |||
Counting question | Excel Worksheet Functions | |||
Counting question | Excel Discussion (Misc queries) |