# Formulas to VBA

F

#### 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 ?

B

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

F

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

R

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

B

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

F

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