Yes, I want that in a VBA

Thanks bob






On Jan 13, 3:46*pm, "Bob Phillips" wrote:
This is worksheet formulae. If you want it in VBA, I would just write a
looping routine.

--
__________________________________
HTH

Bob

wrote in message

...
Hi Bob,

Thanks for the reply,

Assume i want to count the dates which falls under jan.

So, I will use =Count(if(month(A1:A10)=1,))
* * * * * * * *OR
=SUMPRODUCT(--(MONTH(A1:A10)=1))

How do I use this in VBA

is it application.worksheetfunction.count.............. or
application.worksheetfunction.sumproduct....

On Jan 13, 2:35 pm, "Bob Phillips" wrote:



=SUMPRODUCT(--(Sheet1!$B$2:$B$50<""),--(Sheet1!$D$2:$D$50=B$1),
--(MONTH(Sheet1!$B$2:$B$50)=MONTH(LEFT($A2,FIND("
",$A2)-1)&"-"&YEAR(TODAY()))),
--(DAY(Sheet1!$B$2:$B$50)--MID($A2,FIND(" ",$A2)+1,FIND("-",$A2)-FIND("
",$A2)-1)),
--(DAY(Sheet1!$B$2:$B$50)<=--MID($A2,FIND("-",$A2)+1,9)))

--
__________________________________
HTH

Bob

wrote in message

...

Hi All,

I have worksheet, which gives the date and status of the particular
task is completed or not.
sheet 1
Col A Col B Col C Col D
sl.no Date A/C Status
1 02-jan-09 x completed
2 02-jan-09 y completed
3 02-jan-09 z pending

and goes on....

In sheet2 I want a report.
Col A Col B Col C
Weeks completed pending
Jan 1-2
Jan 5-9
Jan 12-16
Jan 19-23
Jan 26-30

in cell B2 below completed I use this formula ( Array Formula)

=COUNT(IF(MONTH(sheet1!$B$2:$B$500)=1,IF(DAY(sheet 1!$B$2:$B$500)=1,IF
(DAY(sheet1!$B$2:$B$500)<=2,IF(sheet1!$C$2:$C$500= sheet2!$B$1,)))))

it will give me the result as 2, for the First week of Jan,
For Second week i will change the days between 5 & 9.

Similarly i will do for the Feb, Mar,.......

Problem is that I personally feel that this is very big...
I guess we can use Pivot table ... by grouping the date , weekly data
we can get...

Any one knows better formula than the above.

And also I want how to convert the above formula to VBA ?

I used this formula
Application.worksheetfunction.count (iif(expre,true,false)......
same way the formula was created but it is giving error 13, type
mismatch
After analysing the problem - what I came know is Month function used
in vba only for single value.

Can we use what I have in formula ?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -