How can count no of count in cells according to same date but different time shift using macro??

Hi all,

Anyone can help?? I need help with this in macro.
I cant find any example. Any similar example and codes to try will be good enough for me.

Thanks

Do you really need a macro?
Do these worksheet formulas do what you want?
Both formulas copied down.

Peter_SSs said:

Do you really need a macro?

Click to expand...

I asked the same thing:

http://www.mrexcel.com/forum/showthread.php?t=567863

Hi,
Peter,thanks for you help. Sorry,I never use this spreadsheet formula before. Can you explain to me how you do it and how it works?? I am abit confused.

To: Peter and T.Valko
I need to do this in macro because this is sort of my mini project so i need to do in macro if it is possible. I am still weak in macro so i am looking for help seeing whether this is possible to be done in macro.

Sorry for any trouble and thank you all.

Please let me know if there are other solutions.

Thanks in advance!

Firstly, please refer to my comment in your other thread about duplicate posts.

mars91 said:

Sorry,I never use this spreadsheet formula before. Can you explain to me how you do it ...

Click to expand...

To use the formula, you copy it from my post above and paste it into the cell shown.

mars91 said:

I need to do this in macro ..

Click to expand...

Well, for the layout shown below and your date of interest in cell E2, here's one way.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CountShifts()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> fNight <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _<br>        "=SUMPRODUCT(--(INT($B$2:$B$#)=$E2),--(MOD($B$2:$B$#,1)<=8.5/24))"<br>    <SPAN style="color:#00007F">Const</SPAN> fDay <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = _<br>        "=SUMPRODUCT(--(INT($B$2:$B$#)=$E2),--(MOD($B$2:$B$#,1)>=0.5))"<br>    <br>    lr = Range("B" & Rows.Count).End(xlUp).Row<br>    Range("F2").Formula = Replace(fNight, "#", lr)<br>    Range("G2").Formula = Replace(fDay, "#", lr)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>