Formulas to VBA

[fi.or.jp.de]

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(sheet1!$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 ?

 

[Bob Phillips]

=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)))

 

[fi.or.jp.de]

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....

 

[Roger Govier]

Hi

Yes, it can be done simply with a Pivot Table if you drag Date to the Row
Area, Status to the Column Area and Status again to the Data area.
Then Group dates by Days>7>Starting at 30 Dec 2008.

If you want to do it by formula, then set up 3 columns on your sheet.
I used columns F,G and H.
In G1 enter Completed and in H1 enter Pending
In F2 enter 02 Jan 09
In F3 enter =F2+7 and copy down as far as required.

In G2 enter
=SUMPRODUCT(($B$2:$B$1000<=$F2)*($B$2:$B$1000>$F1)*($D$2:$D$1000=G$1))
Copy across to H2, then copy G2:H2 down as far as required

Amend the ranges to suit your data, but ensure that ranges are of equal
length. Unless you are using Excel 2007, you cannot give a whole column as a
range.

You might find it easier to create Dynamic range names for the Data.
Insert>Name>Define>
Name Dates Refers to =$B$2:INDEX($B$B,COUNTA($B$B))
Name Status Refers to =$D$2:INDEX($D$D,COUNTA($B$B))

Note. Use Count on column B in each case to ensure ranges are of equal
length.
The formula then becomes
=SUMPRODUCT((Dates<=$F2)*(Dates>$F1)*(Status=G$1))

 

[Bob Phillips]

This is worksheet formulae. If you want it in VBA, I would just write a
looping routine.

--
__________________________________
HTH

Bob

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....

 

[fi.or.jp.de]

Yes, I want that in a VBA

Thanks bob

This is worksheet formulae. If you want it in VBA, I would just write a
looping routine.

--
__________________________________
HTH

Bob


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....




- Show quoted text -