Formulas to VBA
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 -
|